The WEEKDAY function is an important function when you work with a calendar, or when you build a calendar.
- Result returns by the function WEEKDAY.
This function returns a value representing the day of the week, ranging from 1 to 7 (or alternatively, 0 to 6).
With a basic logical test, it's easy to find if a day is a weekend or a weekday - Avoid the mistake according to your localization
In the USA, the first day of the week is Sunday and for most countries in the world, Monday is the first day of the week.
According to your country, you must take care of 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 WEEKDAY function returns 2; WHY? The function, developed by Microsoft, an American company, reflects the U.S. convention where the week starts on Sunday, making Monday the second day.
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 parameter is optional but very useful if you are NOT in USA or Canada. While Monday is the default first day for most countries globally, specifying this argument ensures accurate calendar settings.
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
To be honest, even after 25 years as an Excel developer, I have never seen a workbook using these values. Don't try to use them, use only the values 1 or 2 for the second argument.
Related Articles
This function is very, very, VERY useful for creating a logical test, with the IF function or for conditional formatting.
