Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-02-2021, 12:48 PM
anetteqqq anetteqqq is offline Display week numbers Windows 10 Display week numbers Office 2013
Novice
Display week numbers
 
Join Date: Jun 2021
Posts: 3
anetteqqq is on a distinguished road
Default Display week numbers

Hi


I'm completely noob - but google helps a lot. I want to have the week number of the dates of Start Date, Finish Date and Deadline. This formula seems to work for Start Date, but the other colums show incorrect weeks.

Anyone kind who can help me;

1) make the formulas become correct - show correct week numbers?

2). Explain how the formulas work - their components, maybe then I can work it out myself.....

For example, what does the numbers between [Finish], [Start], etc, mean?


"Week " & CStr(1+datediff("w";"1/1/20" & mid(Year([Finish]);3;2);[Finish]))

"Week " & CStr(1+datediff("w";"1/1/20" & mid(Year([Start]);3);[Start]))

"Week " & CStr(1+datediff("w";"1/1/20" & mid(Year([Deadline]);3);[Deadline]))

Also taking into concideration that weeks start counting from first 4-day week.

Thank you kindly
Reply With Quote
  #2  
Old 06-04-2021, 11:42 AM
ProjectPlanner's Avatar
ProjectPlanner ProjectPlanner is offline Display week numbers Windows 10 Display week numbers Office 2019
Advanced Beginner
 
Join Date: Mar 2021
Location: UK
Posts: 35
ProjectPlanner is on a distinguished road
Default

Replace the semi-colons with a comma, so that the formula for finish is "Week " & CStr(1+datediff("w","1/1/20" & mid(Year([Finish]),3,2);[Finish]))

explanation.

mid(Year([Finish]),3,2) = extracts 2 characters from the year of the finish date starting at the 3rd character. E.g. for "04-Jun-21", year is 2021 and the formula extracts 21.

The 21 from above is then joined to "1/1/20" to form "1/1/2021".

Last edited by ProjectPlanner; 06-04-2021 at 11:43 AM. Reason: spelling error
Reply With Quote
  #3  
Old 06-08-2021, 01:49 AM
anetteqqq anetteqqq is offline Display week numbers Windows 10 Display week numbers Office 2013
Novice
Display week numbers
 
Join Date: Jun 2021
Posts: 3
anetteqqq is on a distinguished road
Default

Quote:
Originally Posted by ProjectPlanner View Post
Replace the semi-colons with a comma, so that the formula for finish is "Week " & CStr(1+datediff("w","1/1/20" & mid(Year([Finish]),3,2);[Finish]))

explanation.

mid(Year([Finish]),3,2) = extracts 2 characters from the year of the finish date starting at the 3rd character. E.g. for "04-Jun-21", year is 2021 and the formula extracts 21.

The 21 from above is then joined to "1/1/20" to form "1/1/2021".


Thank you.
Why only "," on the FINISH formula? Should be on START and DEADLINE too?

The same logic of "mid(Year([Finish]),3,2);[Finish])" is same for START and DEADLINE?

I guess you don't know how to accommodate to make the weeks correct? Year starting with first four day week....etc.

Thanks again
Reply With Quote
  #4  
Old 06-08-2021, 01:50 AM
anetteqqq anetteqqq is offline Display week numbers Windows 10 Display week numbers Office 2013
Novice
Display week numbers
 
Join Date: Jun 2021
Posts: 3
anetteqqq is on a distinguished road
Default

Anyone else who knows and can help? I don't like the date format and want to only show year and week - but I cannot work it out. Maybe it cannot be done?


Quote:
Originally Posted by anetteqqq View Post
Hi
I'm completely noob - but google helps a lot. I want to have the week number of the dates of Start Date, Finish Date and Deadline. This formula seems to work for Start Date, but the other colums show incorrect weeks.

Anyone kind who can help me;

1) make the formulas become correct - show correct week numbers?

2). Explain how the formulas work - their components, maybe then I can work it out myself.....

For example, what does the numbers between [Finish], [Start], etc, mean?


"Week " & CStr(1+datediff("w";"1/1/20" & mid(Year([Finish]);3;2);[Finish]))

"Week " & CStr(1+datediff("w";"1/1/20" & mid(Year([Start]);3);[Start]))

"Week " & CStr(1+datediff("w";"1/1/20" & mid(Year([Deadline]);3);[Deadline]))

Also taking into concideration that weeks start counting from first 4-day week.

Thank you kindly
Reply With Quote
  #5  
Old 06-12-2021, 03:21 AM
ProjectPlanner's Avatar
ProjectPlanner ProjectPlanner is offline Display week numbers Windows 10 Display week numbers Office 2019
Advanced Beginner
 
Join Date: Mar 2021
Location: UK
Posts: 35
ProjectPlanner is on a distinguished road
Default

Your 3 formulas are:

Start Calendar Week = IIf(DatePart("ww",[Start],1,2)=53,Year([Start])-1 & " Week " & DatePart("ww",[Start],1,2),Year([Start]) & " Week " & DatePart("ww",[Start],1,2))

Finish Calendar Week = IIf(DatePart("ww",[Finish],1,2)=53,Year([Finish])-1 & " Week " & DatePart("ww",[Finish],1,2),Year([Finish]) & " Week " & DatePart("ww",[Finish],1,2))

Deadline Calendar Week = IIf(DatePart("ww",[Deadline],1,2)=53,Year([Deadline])-1 & " Week " & DatePart("ww",[Deadline],1,2),Year([Deadline]) & " Week " & DatePart("ww",[Deadline],1,2))


Note that for tasks without a deadline date, there will be an error.







Reply With Quote
  #6  
Old 06-13-2021, 03:42 AM
ProjectPlanner's Avatar
ProjectPlanner ProjectPlanner is offline Display week numbers Windows 10 Display week numbers Office 2019
Advanced Beginner
 
Join Date: Mar 2021
Location: UK
Posts: 35
ProjectPlanner is on a distinguished road
Default

Slight errors in formula now corrected:

Start Calendar Week = IIf(DatePart("y",[Start])<4,Year([Start])-1 & " Week " & DatePart("ww",[Start],1,2),Year([Start]) & " Week " & DatePart("ww",[Start],1,2))

Finish Calendar Week = IIf(DatePart("y",[Finish],1,2)<4,Year([Finish])-1 & " Week " & DatePart("ww",[Finish],1,2),Year([Finish]) & " Week " & DatePart("ww",[Finish],1,2))

Deadline Calendar Week = IIf(DatePart("y",[Deadline],1,2)<4,Year([Deadline])-1 & " Week " & DatePart("ww",[Deadline],1,2),Year([Deadline]) & " Week " & DatePart("ww",[Deadline],1,2))

Last edited by ProjectPlanner; 06-14-2021 at 03:12 AM. Reason: Corrected error in formula
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating 2 percentage fields to show the progress from week to week Loay harhash Project 0 04-03-2018 03:26 AM
Display week numbers Need Excel to recognize week numbers based on specific dates Ktyler Excel 5 02-06-2017 11:08 AM
Table date format: how display week number? codeghi Project 3 07-12-2015 02:40 PM
Display week numbers Display Numbers in CSV file over 15 digits linan123 Excel 1 06-24-2014 01:54 PM
Display Random Numbers as Transition greenbutton PowerPoint 0 03-09-2011 04:43 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:30 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft