How to Calculate the Week Number in Excel?

How to Calculate the Week Number in Excel?
Last Updated on 14/11/2023
Reading time: 2 minutes

There are 2 functions to calculate the week number according to a date in Excel

  1. The WEEKNUM function

    This is the original function that calculated the week number in Excel. But it doesn't respect the conventional rules of most countries in the world.

  2. The ISOWEEKNUM function

    To avoid calculation errors, Microsoft has created another function to calculate the week number.

The WEEKNUM function

In Excel, to return the week number of a date you have the function WEEKNUM.

=WEEKNUM(Date)

Easy? Sure? Well, in fact, it's not so simple. It depends if you are in the USA or in another country. The rule of calculation for the week is different between the USA and the rest of the world. And guess what, because the function has been created by Microsoft, an American company, the function returns the week number according to the American rule.

That's good for the people who work in the USA but NOT for the other countries

The difference between the USA and the rest of the world is simple.

  • In USA, whatever the number of days in the first week of the year, it's the week #1
  • In the rest of the world, the first week must have a least 3 days.

So, if the 1st January is a Saturday, in the USA it will be week#1 but in the rest of the world the week number will be the value of the last week number of the previous year (week #53)

To sum up, the rest of the world uses the ISO 8601 rule to calculate the week number, and the USA uses its own rule.

The ISOWEEKNUM Function

To correct this and to be able to return the correct week number for all the countries in the world, Microsoft provided the function ISOWEEKNUM, in version 2013 and later.

=ISOWEEKNUM(Date)

Look at the difference in results for January the 1st for these different years when using WEEKNUM or ISOWEEKNUM.

Week number of the first day of the year

And the difference is more obvious with the date of the 4th January.

Week number of the 4th January

As you can see, in 2021 and 2022, both formulas don't return the same week number for the 4th of January. That means there will be a difference in your dashboard if you don't use the correct function according to the convention used in your country.

How to calculate the ISO week number in Excel 2013 and previous versions

Excel 2007 and previous versions

The formula to calculate the ISO week number is very complex but it works

=INT((TODAY()-SUM(MOD(DATE(YEAR(TODAY()-MOD(TODAY()-2;7)+3);1;2);{1E+99;7})*{1;-1})+5)/7)

Excel 2010

In this version, you can add a second parameter to the function WEEKNUM.

If you put in the value 21 for the second parameter, the WEEKNUM function will return the same result as the function ISOWEEKNUM does in Excel 2013 and later versions.

=WEEKNUM(Date,21)

Related articles

3 Comments

  1. Ferko
    29/01/2020 @ 16:50

    Hi!

    Actually the rule is in most European countries is that the first week of each year is the first week that starts with a Monday, and not that it needs to have 3 days. So even if January 1st is a Friday it still counts as week 52, and only the next week is week 1.
    Also this difference is not unique to the USA, there are many other countries who counts weeks as the USA, and there are also countries that starts their week (and year) with Saturdays. You can check the map here:
    https://i2.wp.com/kanadabanda.com/wp-content/uploads/2017/09/sunday_7.png

    Reply

    • Frédéric LE GUEN
      29/01/2020 @ 16:56

      Thanks!

      Reply

  2. GAJett
    05/04/2019 @ 16:49

    The understanding of calculating week numbers in the US is a mess. Most on-line search results reference only the ISO standard. The Wikipedia entry (https://en.wikipedia.org/wiki/Week) indicates US weeks are calculated based on the first week of the year is based on the week containing Jan. 1, not a start on Jan. 1. So US week 1 could contain up to 6 December days from the previous year!

    At least one on-line calculator (https://www.calendar-12.com/week_number) returns such results (Week 1 of 2019 contains Dec. 30 - Jan . 5.

    A second (http://www.onlineconversion.com/day_week_number.htm) calculates week 1 in another way, with Jan. 1 in Week 52 of 2018 and week 1 of 2109 beginning Sun. Jan 6.

    Excel, as you describe, starts week 1 of the year on Jan. 1 of the year, with Week 2 beginning the following Sunday. Thus, when Jan. 1 falls on a Saturday, week 1 will have but one day only.

    Note that the Microsoft help documentation does not state why they calculate Week 1 as they do, not cite any standard they are following. My on-line search has not found such a "standard" (although I simply may not have looked long enough).
    Respectfully

    Reply

Leave a Reply

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

How to Calculate the Week Number in Excel?

Reading time: 2 minutes
Last Updated on 14/11/2023

There are 2 functions to calculate the week number according to a date in Excel

  1. The WEEKNUM function

    This is the original function that calculated the week number in Excel. But it doesn't respect the conventional rules of most countries in the world.

  2. The ISOWEEKNUM function

    To avoid calculation errors, Microsoft has created another function to calculate the week number.

The WEEKNUM function

In Excel, to return the week number of a date you have the function WEEKNUM.

=WEEKNUM(Date)

Easy? Sure? Well, in fact, it's not so simple. It depends if you are in the USA or in another country. The rule of calculation for the week is different between the USA and the rest of the world. And guess what, because the function has been created by Microsoft, an American company, the function returns the week number according to the American rule.

That's good for the people who work in the USA but NOT for the other countries

The difference between the USA and the rest of the world is simple.

  • In USA, whatever the number of days in the first week of the year, it's the week #1
  • In the rest of the world, the first week must have a least 3 days.

So, if the 1st January is a Saturday, in the USA it will be week#1 but in the rest of the world the week number will be the value of the last week number of the previous year (week #53)

To sum up, the rest of the world uses the ISO 8601 rule to calculate the week number, and the USA uses its own rule.

The ISOWEEKNUM Function

To correct this and to be able to return the correct week number for all the countries in the world, Microsoft provided the function ISOWEEKNUM, in version 2013 and later.

=ISOWEEKNUM(Date)

Look at the difference in results for January the 1st for these different years when using WEEKNUM or ISOWEEKNUM.

Week number of the first day of the year

And the difference is more obvious with the date of the 4th January.

Week number of the 4th January

As you can see, in 2021 and 2022, both formulas don't return the same week number for the 4th of January. That means there will be a difference in your dashboard if you don't use the correct function according to the convention used in your country.

How to calculate the ISO week number in Excel 2013 and previous versions

Excel 2007 and previous versions

The formula to calculate the ISO week number is very complex but it works

=INT((TODAY()-SUM(MOD(DATE(YEAR(TODAY()-MOD(TODAY()-2;7)+3);1;2);{1E+99;7})*{1;-1})+5)/7)

Excel 2010

In this version, you can add a second parameter to the function WEEKNUM.

If you put in the value 21 for the second parameter, the WEEKNUM function will return the same result as the function ISOWEEKNUM does in Excel 2013 and later versions.

=WEEKNUM(Date,21)

Related articles

3 Comments

  1. Ferko
    29/01/2020 @ 16:50

    Hi!

    Actually the rule is in most European countries is that the first week of each year is the first week that starts with a Monday, and not that it needs to have 3 days. So even if January 1st is a Friday it still counts as week 52, and only the next week is week 1.
    Also this difference is not unique to the USA, there are many other countries who counts weeks as the USA, and there are also countries that starts their week (and year) with Saturdays. You can check the map here:
    https://i2.wp.com/kanadabanda.com/wp-content/uploads/2017/09/sunday_7.png

    Reply

    • Frédéric LE GUEN
      29/01/2020 @ 16:56

      Thanks!

      Reply

  2. GAJett
    05/04/2019 @ 16:49

    The understanding of calculating week numbers in the US is a mess. Most on-line search results reference only the ISO standard. The Wikipedia entry (https://en.wikipedia.org/wiki/Week) indicates US weeks are calculated based on the first week of the year is based on the week containing Jan. 1, not a start on Jan. 1. So US week 1 could contain up to 6 December days from the previous year!

    At least one on-line calculator (https://www.calendar-12.com/week_number) returns such results (Week 1 of 2019 contains Dec. 30 - Jan . 5.

    A second (http://www.onlineconversion.com/day_week_number.htm) calculates week 1 in another way, with Jan. 1 in Week 52 of 2018 and week 1 of 2109 beginning Sun. Jan 6.

    Excel, as you describe, starts week 1 of the year on Jan. 1 of the year, with Week 2 beginning the following Sunday. Thus, when Jan. 1 falls on a Saturday, week 1 will have but one day only.

    Note that the Microsoft help documentation does not state why they calculate Week 1 as they do, not cite any standard they are following. My on-line search has not found such a "standard" (although I simply may not have looked long enough).
    Respectfully

    Reply

Leave a Reply

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