A week number is written as
"YYYY-Www-d" such as 2011-W05-3
where 2011 is of course the year, 05 means the 5th week and the 3 means the 3rd day of the week.
For ISO week numbering, week 1 is defined as the week containing the first Thursday, and day 1 is a Monday. Here's the formula:
=YEAR(theDate) & "-" & "W" & TEXT(1+INT((theDate - DATE(YEAR(theDate + 4 - WEEKDAY(theDate + 6)), 1, 5) + WEEKDAY(DATE(YEAR(theDate + 4 - WEEKDAY(theDate + 6)), 1, 3)))/7), "00") & "-" & WEEKDAY(theDate, 2)
For U.S. week numbering, week 1 is the week containing January 1 and day 1 is a Sunday. Here's the formula:
=YEAR(theDate) & "-" & "W" & TEXT(WEEKNUM(theDate, 1), "00") & "-" & WEEKDAY(theDate, 1)
In these formulas, replace "theDate" with a reference to a cell containing the date.
See this Yearly Calendar with Week Numbers for an example of using the week number in a calendar. You can see the ISO week number in action in my daily planner and weekly planner templates.
1 comment:
Hi, I work in 13 4-week periods during a calendar year. So, I'd like to modify the weeks to show Period, Week and Date, if possible. Is there a way to adjust the weeks formula to show this information? How would this be done?
For example, 19-Mar-2012 is W4 of P3. 26-Mar-2012 is W1 of P4.
Hoping to achieve an end result that changes
"19-Mar-12" to "P3 W4 19-Mar-12"
"26-Mar-12 to "P4 W1 26-Mar-12"
Etc.
Thanks for any advice you can share.
Post a Comment