In Excel, there are 2 functions to return the week number from a date.
- The WEEKNUM function: This function has existed since the beginning of Excel, but it uses the US method, which can lead to interpretation errors.
- The ISOWEEKNUM function: This function has been available since Excel 2013 and returns the week number as determined in Europe.
Different Rules Between the US and Europe
In Europe, week numbers are calculated according to the ISO 8601 standard. This standard specifies that the first week of the year must have at least 4 days.
- In other words, if the first week of the year starts on a Friday, Saturday, or Sunday, those days are not counted as part of the first week of the year.
- However, in North America, this rule does not exist. Week numbers are calculated starting on January 1, even if January 1 falls on a Sunday.
Impact in Excel
To understand the impact of these two formulas on week number calculations, let’s analyze this situation.
Calculation with January 1
The following document lists all the January 1 dates from 2018 to 2030.
- The WEEKNUM function always returns 1 regardless of the first day of the year.
- With ISOWEEKNUM, if the first day of the year is a Friday, Saturday, or Sunday, the function considers January 1 to belong to the previous year’s week.
Week Number with January 4
The following document lists all the January 4 dates from 2018 to 2030.
Notes:
- The week change occurs on a Sunday with the WEEKNUM function.
- To change the week to a Monday, add 2 as the second parameter in the function
=WEEKNUM(date,2).
Week Number in VBA
In VBA, the simplest approach is to use Excel’s calculation functions to return the week number.
Sub Week_Number()
'Week number in European format
Cells(1, 1) = Application.WorksheetFunction.IsoWeekNum(Date)
'Week number in US format
Cells(1, 1) = Application.WorksheetFunction.WeekNum(Date)
End Sub
