To calculate a date from any week number you must take in consideration your rule to calculate a week number
- Countries who use the formula WEEKNUM
If you live in the USA or Canada, there is a specific formula for you
- Countries who use the formula ISOWEEKNUM
For the other countries, there is another formula because the result couldn't be the same
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:
If the week starts on a Sunday, simply remove the +1 at the end
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:
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.