Microsoft Excel
Back to jkp-ads.com

Ron de Bruin
Excel Automation

Microsoft MVP Program

Week numbers in Excel

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse


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 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

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 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 a argument of 21 to the Excel WeekNum function to get the ISO week number as result : =WEEKNUM(A1,21) and in Excel 2013 they finally add a new function named ISOWEEKNUM 

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

 

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 and up WEEKNUM is a Standard worksheet function so you not have the problems above if you share your workbook between different Excel 2007-2013 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 :

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 developed by Ron de Bruin and Norman Harker
Week Numbers Calendar version 2

 

Information about Week Numbers Calendar Version 2.

The Calendar is working with the 1900 and 1904 date system and don't have a problem with 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.

We would like to acknowledge general reference on all date issues to:

Chip Pearson: http://www.cpearson.com/excel/topic.aspx
Dave McRitchie: http://dmcritchie.mvps.org/excel/excel.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 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, Sydney.