Week nummers in Excel

25 jun 2008 - 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.

• 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

 

With the Date in cell B4 you can test the formulas below

ISO 8601:2000 Week Numbering Algorithm (from the late Frank Kabel)
Copy both lines 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 Analysis ToolPak function WEEKNUM

Reliance on Analysis ToolPak is a major annoyance because the Addin may not be installed or checked. 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.
See 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:
=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 :

 

Deze informatie is afkomstig van Ron de Bruin.