The WEEKDAY function is an important function when you work with a calendar, or when you build a calendar.
- The WEEKDAY function returns a value representing the day of the week, ranging from 1 to 7 (or alternatively, 0 to 6)
- Add localization argument. According to your country, you must consider the value of the second argument of the WEEKDAY function.
=WEEKDAY(Date,Localization)
How to use the WEEKDAY function
The WEEKDAY function operates with minimal arguments, just a date
=WEEKDAY(A1)
Yet the returned result may pose confusion 🤔😕
The 13/11/2023 is a Monday and the function returns 2; WHY? 😮 The WEEKDAY function, developed by Microsoft, follows the U.S. convention where the week starts on Sunday, making Monday the second day of the week.
This is why, if you don't use the same US convention for the first day of the week, the results of the WEEKDAY function can insert errors in your report.
Explanation of the second argument
The second argument is optional, but it's particularly useful if you're outside the USA or Canada. While Monday is the default first day of the week in most countries, specifying this argument ensures that your calendar settings are accurate and tailored to your region.
You can set values for the second argument as follows:
- 1 - The week starts on Sunday (value 1) and finishes on Monday (value 7). It's the default value (or empty)
- 2 - The week starts on Monday (1) and finishes on Sunday (7). The result expected by most countries in the word
- 3 - The week starts on Sunday (0) and finishes on Monday (6)
The screenshot displays the different results returned for the same date according to the value of the second argument.
According to your country's convention, you must check the value to set for the second argument.
Other Values for the second argument
You have certainly noticed other values for the second argument of the WEEKDAY function
Remarks! Even after 25 years as an Excel developer, I have never seen a workbook using these values. Only the values 1 or 2 for the second argument are really used.
Related Articles
This function is very, very, VERY useful for creating a logical test, with the IF function or for conditional formatting.
