Weekend or weekday are very easy to identify in your worksheet with the same function; WEEKDAY
- Understand how the WEEKDAY function works
This function returns a value representing the day of the week, ranging from 1 to 7
- Build your test by using the second argument of the WEEKDAY function
The WEEKDAY function could be difficult to use if you don't set the second argument
What is the trick with the second argument of the WEEKDAY function?
In the article on the WEEKDAY function, we have seen that the default value of the second argument is 1.
But if you set the value to 2, your calculations with this function will be much easier 😀👍
Applying this formula to any date ensures a consistent result between 1 (Monday) and 7 (Sunday)
How to test if a date is a weekend?
To check if a date is a weekend or not, you can write this simple test
- If the WEEKDAY function returns 6, it indicates Saturday, and 7 denotes Sunday.
- Utilizing this formula, you can effortlessly determine whether a day falls on a weekend or weekday
Omitting the second argument in WEEKDAY makes the test more complex, requiring an OR function to check for 1 or 7.
Test if a date is a weekday
In the same way, to test if a date is a weekday or not, you can write this formula. This time, you must include the value 5 (Friday) in your result using the 'less than or equal to (<=)' operator.
To highlight the day of the weekend in RED you can use exactly the same formula inside a conditional formatting rule.
- Copy your test (weekend or 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
With this trick, you can highlight the entire row when it's the weekend.