Calculate a date from the week number in Excel

Calculate a date from the week number in Excel
Last Updated on 17/09/2023
Reading time: 3 minutes

To calculate a date from any week number you must take in consideration your rule to calculate a week number

  1. Countries who use the formula WEEKNUM

    If you live in the USA or Canada, there is a specific formula for you

  2. 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

Week number of the first day of the year

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
Comparison week number and first monday

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

New formula to calculate date according week number

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.

Monday of the week 10

1 Comment

  1. Pieter Derckx
    17/09/2023 @ 09:06

    The ISO formula seems to work fine, although in a leap year, the dates are missing 1 day...

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Calculate a date from the week number in Excel

Reading time: 3 minutes
Last Updated on 17/09/2023

To calculate a date from any week number you must take in consideration your rule to calculate a week number

  1. Countries who use the formula WEEKNUM

    If you live in the USA or Canada, there is a specific formula for you

  2. 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

Week number of the first day of the year

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
Comparison week number and first monday

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

New formula to calculate date according week number

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.

Monday of the week 10

1 Comment

  1. Pieter Derckx
    17/09/2023 @ 09:06

    The ISO formula seems to work fine, although in a leap year, the dates are missing 1 day...

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *