Using conditional formatting, we can use a custom formula to highlight all weekend dates from any date range, letting you visually see what day is a weekend.
Contents:
2. Explanation
2.1 How to Enter Custom Conditional Formatting Rule
2.2 How Does this Formula Work?
Formula
This is a conditional formatting function and should be entered in as a new formatting rule. The "A1" in this formula should be replaced with the first cell in your selected range.
= WEEKDAY(A1, 2) > 5
Explanation
How to Enter Custom Conditional Formatting Rule
1. Select the range of cells that you want to apply the conditional formatting to. For example, if your dates are in column A, select that column or the specific cells within that column that you want to highlight.
2. Click on the "Home" tab in the Ribbon, then click on the "Conditional Formatting" dropdown in the "Styles" group.
3. Choose "New Rule". A dialog box will appear.
4. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format".
5. In the "Format values where this formula is true" box, type this formula: =WEEKDAY(A1,2)>5. Replace "A1" with the reference to the first cell in your selected range. This formula uses the WEEKDAY function to test if the date is a Saturday or Sunday.
6. Click on the "Format" button. Choose the format you want to apply when the condition is true, such as a particular fill color to highlight the cell.
7. Click "OK" to close the "Format Cells" dialog box.
8. Click "OK" to close the "New Formatting Rule" dialog box.
Now, all the weekend dates in the selected range will be highlighted with the formatting you selected. This is dynamic, so if you change the date in a cell, the formatting will update based on whether the new date is a weekend or not.
How Does this Formula Work?
This formula is based around the WEEKDAY function. The WEEKDAY function returns a number between 1 and 7, representing the day of the week. The first day of the week is determined by the second argument in the function.
Here's how the second argument changes the output:
Numbers 1-7 represent Sunday-Saturday.
Numbers 2-8 represent Monday-Sunday.
In the formula =WEEKDAY(A1,2)>5, we're using 2 as the second argument, which means Monday is represented by 1 and Sunday by 7. So, Saturday becomes 6 and Sunday becomes 7.
The >5 part of the formula creates a condition where only numbers larger than 5 are considered as TRUE. Therefore, only Saturday (6) and Sunday (7) will return TRUE.
So, in the context of a conditional formatting rule, this formula will apply the formatting to any cells where the formula evaluates as TRUE - that is, any cells containing Saturday and Sunday dates.