↑ Return to Date & Time

Week functions

When you want to make calculation on the week, the calculation can becomes very complex and error-prone. In this page you will find the different method to use the week's functions.

Weekday function

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)

Workday function

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 :)

Week number

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

Excel 2007

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.

Excel 2010

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

Leave a Reply