The basics of conditional formatting for dates
Dates play a big role in many workbooks. With the tool conditional formatting you can create rules to highlight critical dates easily
To find conditional formatting for dates, go to
- Conditional Formatting
- Highlight Cell Rules
- A Date Occurring
Default rules for the Dates
You can select the following date options, ranging from yesterday to next month and with the second drop down list, you select the format to apply when the rule is TRUE.
But honestly, these default rules are not really useful. But if you create you own formulas, you can highlight dates like birthday, weekend, public holidays, ...
Below are step-by-step instructions for a few of my favorite conditional formats for dates.
With the conditional formatting tool, you can automatically change the colors of weekends by basing the format on the WEEKDAY function.
Create a new rule
To change the color of the weekends, open the menu Conditional Formatting > New Rule
In the next dialog box, select the menu Use a formula to determine which cell to format.
Write your formula
In the text box Format values where this formula is true, enter the following WEEKDAY formula to determine whether the cell is a Saturday (6) or Sunday (7):
- The parameter 2 means Saturday = 6 and Sunday = 7.
- You must add a test (>5) to return TRUE or FALSE
Note: In this case, you must lock the reference of the rows in order to stay on the date (row = 5)
Add color when the test is TRUE
Then, customize the format of your condition by clicking on the Format button and you choose a fill color (orange in this example).
- Select the tab Fill
- Choose one color
Apply the rule for a range of cells
At this step, the conditional formatting is for one cell only. So we must extend the range of cells for this rule.
Click OK, then open Conditional Formatting> Manage Rules
- Select This Worksheet to see theworksheet rules instead of thedefault selection.
- Then in the Applies to section, change the range that corresponds to your initial selection when creating your rules to extend it to the whole column.
And now, all the dates equal to a week-end have a different color 😎😍😃
Highlighting specific date like holidays
When you want to change the color of specific dates (like public holidays), the formula for the rule is not the same.
In this situation, we have to create a rule that count if a day from a list of holidays is equal to the date in the calendar. So here we are going to use the COUNTIF function
Let's say, we have the US public holidays in column AH
- Open the menu Conditional Formatting > New Rule.
- Write the following formula.
The explanation of the formula is the following . We count the number of time the dates of the column AH are equal to the date of our calendar. If the result =1 that means the public holiday = the date of the calendar.
Then, in the dialog box Manage Rules, select the range B4:AF11. If you want to highlight the holidays over the weekends, you move the public holiday rule to the top of the list.