Week Numbers in Excel (Win and Mac)

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.

• ISO Week: 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.
• Excel WEEKNUM function with an optional second argument of 1 (default). Week one begins on January 1st; week two begins on the following Sunday
• Excel WEEKNUM function with an optional second argument of 2. Week one begins on January 1st; week two begins on the following Monday.
• 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 do not have a standard worksheet function for the ISO week number before Excel 2010 and also not for the simple week numbering system.

Worksheet formulas for Week numbers

ISO Week Numbers

Note: There is no built-in worksheet function for ISO weeks in Excel before Excel 2010, the formula and the function below will work in all Excel versions so it is a good option to use in your workbook if it will be used in different Excel versions. But FYI In Excel 2010(and also in Excel 2011 for the Mac) they add an argument of 21 to the Excel WeekNum function to get the ISO week number as result : =WEEKNUM(B4,21) and in Excel 2013 they finally add a new function named ISOWEEKNUM. So in Win Excel 2013 and higher and in Mac Excel 2016 and higher you can use the ISOWEEKNUM function.

Copy the worksheet function below and paste it in a worksheet cell and it check the Date in B4.

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

You can also copy this UDF in a standard module and use this 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

The Excel worksheet function WEEKNUM

Reliance on Analysis ToolPak is a major annoyance because the Add-in may not be installed or checked in Excel versions before Excel 2007. A default Excel installation has it unchecked. Also, there are international difficulties where you use ATP formulas because these formulas are not translated by Excel if you open the workbook in a different Excel language version.

Note: In Excel 2007 and up WEEKNUM is a Standard worksheet function so you not have the problems above if you share your workbook between different Excel 2007-higher language versions and if the add-in is unchecked.

You can use this two replacement functions from Daniel M to avoid problems above so they work in every Excel version.

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

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

Simple Week Numbering

Note: There is no built-in worksheet function for Simple week numbering in Excel.
Copy the function below and paste it in a worksheet cell.
=INT((B4-DATE(YEAR(B4),1,1))/7)+1

Week Calendar file (print one page with the whole year on it)

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 an other year you only have to change one cell (the year). There is a separate sheet for the following week numbering systems :

• ISO Week: 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.
• Excel WEEKNUM function with an optional second argument of 1 (default). Week one begins on January 1st; week two begins on the following Sunday
• Excel WEEKNUM function with an optional second argument of 2. Week one begins on January 1st; week two begins on the following Monday.
• 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).

Download the Calendar file developed by Ron de Bruin and Norman Harker:
Week Numbers Calendar