ISO Week Number

Before showing you the formulas, it is important to keep in mind that there are 2 ways to count weekly numbers

• The North American method (the formulas WEEKNUM in Excel)
• The international method (the formula ISOWEEKNUM)

In Europe, the calculation of week numbers is governed by the ISO 8601 standard. This standard considers that the first week of the year must have at least 4 days. When the first week of the year begins on a Friday, Saturday or Sunday, these days are not considered the first week of the year.

But for North America, this rule does not exist. The calculation of week numbers starts on January 1st, even if January 1st is a Sunday.

With Excel, this difference can be displayed as follows:

• With the WEEKNUM function, regardless of the first day of the year, the function always returns the value 1.
• With ISOWEEKNUM, if the first day of the year is a Friday, Saturday or Sunday, the function considers that the date of January 1 belongs to the week of the previous year.

This notion is important in the result we are going to achieve

Common formula to return a date according to the week number

The common formula to return on the first day of a week (the week begins on a Monday) is as follows:

=DATE(Year,1,1)+(Week-1)*7-WEEKNUM(DATE(Year,1,1),2)+1

If the week starts on a Sunday, simply remove the +1 at the end

=DATE(Year,1,1)+(Week-1)*7-WEEKNUM(DATE(Year,1,1),2)

This formula returns a wrong result for many countries

But this formula does not take into account the problem of ISO week numbers. This function works for the United States and Canada, but not for other countries 

To find out, the following table shows the problem

• The value of the years in column A
• The day of the first of January in column B; =DATE(A2,1,1)
• The result of the calculation of the date from the first week
• The ISO week number

In the previous table, we see that the date calculated for the first week is often in December. In fact, the formula returns the month of January only if January 1 is a Monday. 

Now, if we consider the year 2021, we can see that 01/01/2021 is a Friday. And so, the calculated Monday will be 28/12/2020. But in this case, we are no longer in week 1 (ISO week number)

Hence a one-week delay compared to calculation standards in Europe. Therefore, the previous formula must be adapted to handle the ISO week number rule.

Formula that takes into account the ISO week rule

Taking into account the previous remarks, the formula that allows calculating a date from the week number respecting the iso week rule is as follows:

=DATE(Year,1,1)+IF(WEEKDAY(DATE(Year,1,1),2)<5,(WeekNum-1)*7,WeekNum*7)-WEEKDAY(DATE(Year,1,1),2)+1

As you can see, the calculation of the first Monday of the first week of the year is now correct 😃👍

So now, you can replace the week number with any values between 1 and 52 and the result will be correct for the ISO week number system.