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.