«

»

The correct value of the week number

The function WEEKNUM

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 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, american company, the function returns the american rule.

That's good for the people who work in USA but for the other it's not correct

Rule to calculate the week number

The difference between 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 USA it will be the week#1 but in the rest of the world the week number doesn't exist.

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

Function ISOWEEKNUM

So correct this and to be able to return the correct week number for all the countries in the world, Microsoft has create for the version 2013, the function ISOWEEKNUM.

=ISOWEEKNUM(Date)

Look the difference of calculation on the for January the 1st for these different years

And for the 4th of January

How to calculate the ISO week number before Excel 2013

Excel 2007 and before

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 the value 21 (hmmm, ok, why not 🤔🙄), the WEEKNUM function will return the value like the function ISOWEEKNUM.

=WEEKNUM(Date,21)

 

Permanent link to this article: https://www.excel-exercise.com/correct-value-week-number/


Leave a Reply

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