2

According to Microsoft, the Excel function WEEKNUM is used to return the week number for the specified date based on a specific model (1 or 2). The week number indicates where the week falls numerically within a year. Model 2 means that the week containing the first Thursday of the year is the first week of the year and is numbered as week 1. This system is the methodology specified in ISO 8601, commonly known as the European week numbering system.

So, assuming dd/mm/yyyy format, WEEKNUM(02/01/2024, 2) should return 1, because 2 January 2024 is Thursday.

However, if I use that function on the week starting on Monday, 30 December 2024 and ending on Sunday, 5 January 2025, I get

30/12/2024  53  Monday
31/12/2024  53  Tuesday
01/01/2025  1   Wednesday
02/01/2025  1   Thursday 
03/01/2025  1   Friday 
04/01/2025  1   Saturday 
05/01/2025  1   Sunday

As far as I know, the number of week should be THE SAME independently on the provided date. Should I assume that this is an Excel bug or is intended?

Dario de Judicibus
  • 121

1 Answers1

2

Try using ISOWEEKNUM() function:

enter image description here

• Formula used in cell E2

=ISOWEEKNUM(B2:B9)

ISOWEEKNUM() follows ISO Standards where it takes the dates and returns week number from 1-54, note that weeks begin on Monday and week number 1 is assigned to the first week of the year which contains the Thursday.

While one can return the same output with WEEKNUM() as with ISOWEEKNUM() if the 2nd param is set to 21

enter image description here

=WEEKNUM(+B2:B9,21)
Mayukh Bhattacharya
  • 4,486