This function will return a number corresponding to the day in a week. But this function has been developed in the United States, the first day of the week is the default on a Sunday. Therefore, we must pay attention to the second parameter of the function to avoid an issue in your result (very common mistake). With Excel 2010, new parameters have been added (Take care for compatibility between versions).
Value for the second parameter (all versions)
- 1: The week starts on a Sunday (1) and ends on a Saturday (7)
- 2: The week starts on Monday (1) and ends on Sunday (7)
- 3: The week starts on Monday (0) and ends on Sunday (6)
And only for Excel 2010
- 11: The week starts on Monday (1) and ends on Sunday (7)
- 12: The week starts on a Tuesday (1) and ends on Monday (7)
- 13: The week starts on a Wednesday (1) and ends on a Tuesday (7)
- 14: The week starts on a Thursday (1) and ends on a Wednesday (7)
- 15: The week starts on a Friday (1) and ends on a Thursday (7)
- 16: The week starts on a Saturday (1) and ends on Friday (7)
- 17: The week starts on a Sunday (1) and ends on a Saturday (7)
This feature is very useful for those who develop schedules. Indeed, when you have a start date and you want to add a number of days, you must avoid to count Saturday or Sunday.
In the following example, you have the starting dates and also the number of days to achieve them. With the functionWORKDAY you only have to integrate two parameters: the start date and the number of days to add. And now the result is correct
Calculating the week numbers is not easy because there a difference between North America and Europe.
Rule: In Europe, the calculation of week numbers are governed by the rule ISO 8601. This rule considers that the first week of the year must be at least 4 days. A year that begins on a Friday, Saturday or Sunday is not considered as a week. For North America, this rule does not exist and the calculation of numbers begins with the first day of January (Sunday the 1st January is the week #1 and Monday the 2nd January is the week #2)
When the function was developed by Microsoft developers, the rule for the calculation of North America was chosen. But faced with criticism, the formula has evolved to allow to take into account the two rules. This has been introduced since Excel 2010.
Before Excel 2007
The function returns the number of weeks with the North American method. To return the value for Europe (ISO 8601), you must use the complex formula below
For this version, Microsoft introduced a second parameter completely useless because it doesn’t take into account the differents methods
The two possible values are 1 (week starts on Sunday) or 2 (week starts on Monday). No relation to the ISO 8601 standards for calculating so with Excel 2007, we must still use the complex formula.
In this version, the second parameter has been modified to take into account the two calculation methods. New values have been added for the second parameter but only one is useful: it’s the value 21 (European method).