Week numbers in Excel
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: 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 not have a standard worksheet function for the ISO week number
and simple week numbering system.
Worksheet formulas for Week numbers
With the Date in cell B4 you can test the formulas below
ISO Week Numbers
Note: There is no built-in worksheet function for ISO weeks in Excel.
Copy the worksheet function below and paste it in a worksheet cell.
=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)
You can find more information about ISO dates and week numbers on this page
http://www.rondebruin.nl/isodate.htm
The Excel worksheet function WEEKNUM
Reliance on Analysis ToolPak is a major annoyance because the Addin 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 WEEKNUM is a Standard worksheet function so you not have the problems above
if you share your workbook between different Excel 2007 language versions.
Important : If you not have Office 2007 SP2 installed read the info on this page :
http://www.rondebruin.nl/atp.htm
You can use this two replacement functions from Daniel M to avoid problems.
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
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 :
1) ISO Week numbering: Week 1 starts on Monday of the week with the first
Thursday of the Calendar Year.
2) Excel WEEKNUM function (optional second argument of 1 (default)). Week 1
starts 1-Jan with subsequent weeks starting on a Sunday and final week ending on 31-Dec.
3) Excel WEEKNUM function (optional second argument of 2). Week 1 starts
1-Jan with subsequent weeks starting on a Monday and final week ending on 31-Dec.
4) Simple week number. Week 1 starts on the first day of the year.
Download the Calendar file
Week Numbers Calendar.zip (File date : 27-Feb-2005)
Information about Week Numbers Calendar Version 2.
Since releasing the first version of the Calendar in January 2005, we have
made a few changes that, hopefully ensure usability on all versions of
Excel.
a) We have removed VBA User Defined Functions (UDFs) and replaced them by
equivalent formulas. Although UDFs simplify formulas, the disadvantage is
that those formulas get disabled where the user has Very High or High
security settings in place or has to confirm acceptance if settings are set
to Medium.
b) The original version was limited to the (default) 1900 Date Serial number
system used by Excel. We have removed this limitation and the calendar will
now survive a switch to the alternative 1904 Date System. That sounds easy,
but it involved some rather nasty changes to formulas.
c) The day names on all sheets are now taken from dates and formatted as
days. This ensures that the day names are automatically in the language of
the Excel version used.
d) We have corrected the Excel 1900 Leap Year error. Excel (for
compatibility reasons) followed the error made by earlier spreadsheet
packages which was to show a 29-Feb in 1900. Apart from now skipping
29-Feb-1900, our calendar also allows reading of the correct day names for
dates before 1-Mar-1900. Although week numbers are unlikely to be wanted for
1900, it does now mean that there is now a source for a correct 1900
calendar.
e) Although calendars are provided for the Analysis ToolPak's WEEKNUM week
numbering algorithm, the Calendar avoid use of Analysis ToolPak functions.
Accordingly, it does not rely on Analysis ToolPak being installed and
selected as an Addin.
We would like to acknowledge general reference on all date issues to:
Chip Pearson MVP (Excel): http://www.cpearson.com/excel/topic.aspx
Dave McRitchie MVP (Excel): http://www.mvps.org/dmcritchie/excel/xlindex.htm
Daniel M 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 ISO year start were derived from a UDF written by
John Green MVP (Excel), 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
Dutch article on Office Online : Weeknummers berekenen in Excel
http://office.microsoft.com/nl-nl/excel/HA102581001043.aspx
differences that you should be aware of. Excel can work with any of these systems.
1) 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.
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 not have a standard worksheet function for the ISO week number
and simple week numbering system.
Worksheet formulas for Week numbers
With the Date in cell B4 you can test the formulas below
ISO Week Numbers
Note: There is no built-in worksheet function for ISO weeks in Excel.
Copy the worksheet function below and paste it in a worksheet cell.
=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
http://www.rondebruin.nl/isodate.htm
The Excel worksheet function WEEKNUM
Reliance on Analysis ToolPak is a major annoyance because the Addin 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 WEEKNUM is a Standard worksheet function so you not have the problems above
if you share your workbook between different Excel 2007 language versions.
Important : If you not have Office 2007 SP2 installed read the info on this page :
http://www.rondebruin.nl/atp.htm
You can use this two replacement functions from Daniel M to avoid problems.
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
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 :
1) ISO Week numbering: Week 1 starts on Monday of the week with the first
Thursday of the Calendar Year.
2) Excel WEEKNUM function (optional second argument of 1 (default)). Week 1
starts 1-Jan with subsequent weeks starting on a Sunday and final week ending on 31-Dec.
3) Excel WEEKNUM function (optional second argument of 2). Week 1 starts
1-Jan with subsequent weeks starting on a Monday and final week ending on 31-Dec.
4) Simple week number. Week 1 starts on the first day of the year.
Download the Calendar file
Week Numbers Calendar.zip (File date : 27-Feb-2005)
Information about Week Numbers Calendar Version 2.
Since releasing the first version of the Calendar in January 2005, we have
made a few changes that, hopefully ensure usability on all versions of
Excel.
a) We have removed VBA User Defined Functions (UDFs) and replaced them by
equivalent formulas. Although UDFs simplify formulas, the disadvantage is
that those formulas get disabled where the user has Very High or High
security settings in place or has to confirm acceptance if settings are set
to Medium.
b) The original version was limited to the (default) 1900 Date Serial number
system used by Excel. We have removed this limitation and the calendar will
now survive a switch to the alternative 1904 Date System. That sounds easy,
but it involved some rather nasty changes to formulas.
c) The day names on all sheets are now taken from dates and formatted as
days. This ensures that the day names are automatically in the language of
the Excel version used.
d) We have corrected the Excel 1900 Leap Year error. Excel (for
compatibility reasons) followed the error made by earlier spreadsheet
packages which was to show a 29-Feb in 1900. Apart from now skipping
29-Feb-1900, our calendar also allows reading of the correct day names for
dates before 1-Mar-1900. Although week numbers are unlikely to be wanted for
1900, it does now mean that there is now a source for a correct 1900
calendar.
e) Although calendars are provided for the Analysis ToolPak's WEEKNUM week
numbering algorithm, the Calendar avoid use of Analysis ToolPak functions.
Accordingly, it does not rely on Analysis ToolPak being installed and
selected as an Addin.
We would like to acknowledge general reference on all date issues to:
Chip Pearson MVP (Excel): http://www.cpearson.com/excel/topic.aspx
Dave McRitchie MVP (Excel): http://www.mvps.org/dmcritchie/excel/xlindex.htm
Daniel M 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 ISO year start were derived from a UDF written by
John Green MVP (Excel), 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
Dutch article on Office Online : Weeknummers berekenen in Excel
http://office.microsoft.com/nl-nl/excel/HA102581001043.aspx