Excel has had the WEEKNUM function for as long as I can remember, but it is very confusing, and you tend to get it wrong more often than you get it right. This is what it looks like when you type the WEEKNUM function:

Who’s to know that it is actually the last option (21) that’s the right one for most users?

Thankfully, a few years ago, Microsoft finally launched the new ISOWEEKNUM function, which I’ll describe in a moment. But first, let’s look at the different ways to calculate week numbers.

Take a look at this picture. The first day of the years 2020, 2021 and 2022 have different week numbers!

There are different ways to define the week number of a specific date. According to one definition, January 1st would always be in Week number 1, and then the week number changes every Sunday. In that case, week number 1 would only have one day in 2022, because January 1st is a Saturday, and the week number changes on Sunday, January 2nd. This is the default definition if you use the WEEKNUM function in Excel

The week numbers on the picture above are calculated according to the official ISO standard, where the week starts on a Monday and the week containing the first Thursday of the year is considered week 1.

The old WEEKNUM function has 8 options for return type in its second argument:
– The return types 1, 2, 11, 12, 13, 14, 15, 16 and 17 are all based on the rule that the week of January 1st is week 1. With option 1, the week begins on a Sunday, with option 2 the week begins on Monday, option 11 is Tuesday, 12 is Wednesday, etc.
– Return type 21 was introduced in Excel 2010, and this is the official ISO Week Date System where the week starts on Monday and the week containing the first Thursday of the year is considered week 1.

The ISO system is commonly known as the European week numbering system, and since week numbers are used a lot in Europe, the old and confusing WEEKNUM function, where you have to choose return type 21, has caused countless errors in a large number of companies and governments across Europe. If you forget the second argument in the formula, Excel will assume that you want option 1.

Finally, in Excel 2013, Microsoft introduced a new function: ISOWEEKNUM. This function doesn’t have a second argument – it’s programmed to return the right week number according to the ISO standard.

As you can see, the old WEEKNUM function is risky, and there is no reason to use it anymore. In this example you see how the same date can have three different week numbers depending on which definition you use:

More on Date and Time in Excel:

• Highlight Weekends in Excel with Conditional Formatting
• Hide Future Dates in Excel with Conditional Formatting
• How to Summarize Hours in Excel and Show the Right Result

Are you using a non-English version of Excel? Click here for translations of the 140 most common functions in 17 different languages:

Catalan
Czech
Danish
Dutch
Finnish
French
Galician
German
Hungarian
Italian
Norwegian
Polish
Portuguese (Brazilian)
Portuguese (European)
Russian
Spanish
Swedish
Turkish