Separating weekends and weekdays is a basic job in Excel. For example, when you have to build an automatic calendar.
The function WEEKDAY
This function returns a value between 1 and 7 corresponding to the value of the day of the week.
This function is very, very, VERY useful for creating a test in a IF function or for a conditional formatting.
Explanation of the second parameter
The second parameter is optional but very useful if you are NOT in USA or Canada.
In USA, the first day of the week is Sunday. So, if you don't specify this parameter 1 = Sunday.
But for most countries in the world, the first day of the week is Monday. This is why it is important to indicate the value of this parameter in function of your localization.
Basic value for the second parameter
For all the versions of Excel, the value for the second parameter could be
- 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)
- 3 - The week starts on Sunday (0) and finishes on Monday (6)
The screenshot below displays the different results returned for the same date (01/01/2018) in function of the second parameter chosen.
Upgrading the value in newer versions of Excel
In Excel version 2010 and higher, new values have been added but it's not really useful except for some Arabic countries where the weekend is not Saturday and Sunday.
- 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 - Week starts a Wednesday (1) and finish a Tuesday (7)
How to test if a date is a weekend?
If you want to check if a date is a weekend or not, you can write this simple test with the WEEKDAY function.
Or if you don't want to specify the second parameter, you can write your test like this:
The following screenshot displays:
- Column A:A list of dates
- Column B: The value of the date in words
- Column C: The value returned by the function WEEKDAY (parameter 2)
- Column D: The result of the test
- Column E: The formulas used
To highlight the day of the weekend in RED you can use exactly the same formula inside a conditional formatting rule.
- Copy your formula WEEKDAY
- Select all the cells of your document
- Open the menu Home>Conditional Formatting>Manage Rules>New Rules
- Select the option Use a formula to determine which cells to format
- Paste the formula
- Change the format
And the result is:
- Weekly calendar 2020 in one formula
- How to Highlight a Birthday Automatically
- Convert YYYYMMDD to DD/MM/YYYY
- First day – Last day in Excel
- Date Format in Excel
- Add Days Excluding the Weekend
- Split Time and Date
- Function DATE – YEAR – MONTH – DAY
- Using conditional formatting to highlight dates
- NOW & TODAY