Formulas for Week Numbering in Excel

Using the ISO or U.S. week number is common when calendars and other templates are used for project planning.
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:

JZ said...

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"

Thanks for any advice you can share.