In Excel, when you have to calculate week days or week number it’s easy to make a lot of mistake and then your result will be wrong. In this article, you will find a lot of examples to avoid to make errors.
The function WEEKDAY will return a value between 1 and 7 corresponding of the value of the day in the week.
Explanation of the second parameter
The function has been created by Microsoft and Microsoft is in the United States. In the USA, the first day of the week is Sunday but in Europe the first day of the week is Monday. So, in function of the region of the world where do you live, it’s important to set a value of the second parameter.
For all the versions of Excel, the value for the second parameters are
- 1 – The week starts a Sunday (1) and finish a Monday (7). It’s the default value.
- 2 – The week starts a Monday (1) and finish a Sunday (7)
- 3 – The week starts a Sunday (0) and finish a Monday (6)
With Excel 2010 and the earlier version, new values have been added.
- 11 – The week starts a Monday (1) and finish a Sunday (7)
- 12 – The week starts a Tuesday (1) and finish a Monday (7)
- 13 – The week starts a Wednesday (1) and finish a Tuesday (7)
- 14 – The week starts a Thursday (1) and finish a Wednesday (7)
- 15 – The week starts a Friday (1) and finish a Thursday (7)
- 16 – The week starts a Saturday (1) and finish a Friday (7)
- 17 – The week starts a Sunday (1) and finish a Monday (7)
Alone, the function is not really useful but if you add it in a test, like for the function IF, SUMIFS, COUNTIFS and also with the conditional formatting, then this function is really useful.
For instance, in the article of the creation of and automatic calendar, I have used the function WEEKDAY in a conditional formatting to change the color of the week-ends
When you have to build a schedule for your team, it’s necessary to avoid to include the week-ends in your calculation. So you can not directly write a function like the next formula otherwise you will have
And if you use the formula WORKblabla
The week number
The week number is returned by the function WEEKNUM. But, like for the function WEEKDAY, the function has been created in USA and the rule of calculation is different between the USA and Europe (I don’t know which rule is used in Asia but send me a message to tell it to me).
Rule : In Europe, the calculation of the week numbers is governed by ISO 8601. This standard considers the first week of the year must have at least 4 days. When a year starts on a Friday, Saturday or Sunday, this is not consider as the week number 1. In North America, this rule does not exist and calculating week numbers begins on January 1st, although the January 1 is a Sunday.
When the function has been developed by the Microsoft developers, it’s the North America rule that have been chosen. But the face to criticism, the formula has evolved to take into account the 2 rules. This change was introduced in Excel 2010 and also in Excel 2013 the function ISOWEEKNUM has been created.
Excel 2003 and before
The WEEKNUM function returns systematically the value of North America. To return the value for Europe (ISO 8601), you have to write this very complex formula.
For this release, Microsoft has introduced a second parameter completely useless because it doesn’t correct the problem of the calculation 🙁
The possible values for this parameter are 1 (Week starts Sunday) or 2 (the week starts on Monday). No relation with thr standards ISO 8601. To calculate the week number for Europe, we still need the previous complex formula.
In this version, the second parameter has been modified to take into account the two calculation methods. New parameters have been added for the second parameter but only the value 21 is useful to return the week number according to ISO 8601 (European method).
Microsoft has finally created a new function in Excel 2013 to return the week number as calculated in Europe to harmonize the various techniques for calculating week numbers.