Week Numbers in Excel

Today’s author is, Ron de Bruin, an Excel MVP. You can find more useful tips from Ron and links to Excel add-ins at his website: http://www.rondebruin.nl/

There are four primary week numbering systems in use worldwide. Each system has subtle differences that you should be aware of. Excel can work with any of these systems:

1)  ISO Week number: The International Organization for Standardization (ISO) ISO8601:2000 Standard.
All weeks begin on a Monday. Week one starts on Monday of the first week of the calendar year with a Thursday.
2)  Excel WEEKNUM function with an optional second argument of 1 (default).
Week one begins on January 1st; week two begins on the following Sunday.
3)  Excel WEEKNUM function with an optional second argument of 2.
Week one begins on January 1st; week two begins on the following Monday.
4)  Simple week numbering.
Week one begins on January 1st, week two begins on January 8th, and week 53 has only one or two days (for leap years).

Note: Excel does not have a standard worksheet function for the ISO week number and simple week numbering system.

Worksheet Formulas for Week Numbers

The following sections assume that you have a date in cell B4 for testing the week number formulas.

ISO Week Numbers

There is no built-in worksheet function for ISO week numbers in Excel. Copy the following formula and paste it in a worksheet cell to return an ISO week number:

=INT((B4-DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3)+WEEKDAY(DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3))+5)/7)

Alternatively, you can open the Visual Basic editor, click Module on the Insert menu, and then copy this user-defined function (UDF) into the module. After adding this UDF to your workbook, you can use it like a built-in function =IsoWeekNumber(B4)

Public Function IsoWeekNumber(d1 As Date) As Integer
' Attributed to Daniel Maher
    Dim d2 As Long
    d2 = DateSerial(Year(d1 - WeekDay(d1 - 1) + 4), 1, 3)
    IsoWeekNumber = Int((d1 - d2 + WeekDay(d2) + 5) / 7)
End Function

In VBA you could also use:

DatePart("ww",date,vbMonday,vbFirstFourDays)

However, because there is a bug with the VBA DatePart function with respect to ISO week numbers, it is not a good option to use it. Use the VBA IsoWeekNumber function above to avoid problems. For more information on the DatePart method bug, see this KB article: BUG: Format or DatePart Functions Can Return Wrong Week Number for Last Monday in Year.

You can find more information about ISO dates and week numbers on this page: http://www.rondebruin.nl/isodate.htm

The Excel WEEKNUM Function

Reliance on the Analysis Toolpak in Excel versions before Excel 2007 can create problems because the add-in may not
be installed by users of your spreadsheets (a default Excel installation has it unchecked in setup). Also, there are difficulties for international users when you use Analysis Toolpak formulas because these formulas are not translated by Excel if you open the workbook in a different Excel language version.

Note: In Excel 2007 WEEKNUM is a standard built-in worksheet function, so you will not have the problems above if you share your workbook between different Excel 2007 language versions.

Important: If you do not have Office 2007 SP2 installed, read the information on this page: http://www.rondebruin.nl/atp.htm

You can use these two replacement functions from Daniel Maher to avoid problems.

Replace =WEEKNUM(B4,1) with:

=1+INT((B4-(DATE(YEAR(B4),1,2)-WEEKDAY(DATE(YEAR(B4),1,1))))/7)

Replace =WEEKNUM(B4,2) with:

=1+INT((B4-(DATE(YEAR(B4),1,2)-WEEKDAY(DATE(YEAR(B4),1,0))))/7)

Simple Week Numbers

There is no built-in worksheet function for simple week numbering in Excel. Copy the following function and paste it in a worksheet cell to return simple week numbers:

=INT((B4-DATE(YEAR(B4),1,1))/7)+1

The Week Calendar File

The week calendar file shows you all the dates and week numbers from a certain year on one printable page. If you want to have a week calendar from another year you only have to change one cell (the year). There is a separate sheet for each of the four week numbering systems listed at the start of this post.

Download the Calendar file
Week Numbers Calendar.zip (File date : 27-Feb-2005)

Acknowledgements

I would like to acknowledge general reference on all date issues to:
Chip Pearson: http://www.cpearson.com/excel/topic.aspx
Dave McRitchie: http://www.mvps.org/dmcritchie/excel/xlindex.htm
Daniel Maher has also published numerous simplifications of date formulas some of which were used or adapted in producing the calendar.
The late Frank Kabel Frank created the ISO week number worksheet function on this page.
The base formula used for the ISO year start in the calendar file were derived from a UDF written by John Green, Sydney.

More Information
ISO Date Representatation and Week Numbering:
http://www.rondebruin.nl/isodate.htm
You should refer to Chip Pearson's web site for an exposition on Week Number implementation:
http://www.cpearson.com/excel/weeknum.htm
Implementing Week-Numbering Systems and Date/Time Representations:
http://msdn.microsoft.com/en-us/library/bb277364.aspx