Using conditional formatting to highlight dates

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

  1. Home
  2. Conditional Formatting
  3. Highlight Cell Rules
  4. 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.

Highlighting weekends

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):

=WEEKDAY(B$5,2)>5

  • 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

  1. Select This Worksheet to see theworksheet rules instead of thedefault selection.
  2. 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

  1. Open the menu Conditional Formatting > New Rule.
  2. Write the following formula.

=COUNTIF($AH$4:$AH$16,B$5)=1

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.

Related posts


Permanent link to this article: https://www.excel-exercise.com/using-conditional-formatting-highlight-dates/


1 comment

    • Trudy on 17/04/2019 at 17:27
    • Reply

    please help me. I have been working on this for 4 hours. I need to know how to write formula to highlight current month and next month in yellow, and highlight a month or any month that is out of date for the month in red. i am tracking upcoming license plate renewals. I want a two month warning and any month past due in red. I prefer to use an icon but a cell highlight is fine.

Leave a Reply

Your email address will not be published.