Skip to main content
Microsoft 365
Subscribe

Using conditional formatting to highlight dates in Excel

Updated 12/16/22: Stay up to date on the latest from Excel and download Excel templates today.

Date functions in Microsoft Excel make it possible to perform date calculations, like addition or subtraction, resulting in automated or semi-automated worksheets. The NOW function, which calculates values based on the current date and time, is a great example of this.

Taking this functionality a step further, when you mix date functions with conditional formatting, you can create spreadsheets that display date alerts automatically when a deadline is near or differentiates between types of days, like weekends and weekdays.

Security practitioner or information worker working from home office.

Microsoft Excel

Get a better picture of your data.

The basics of conditional formatting for dates

To find conditional formatting for dates, go to:

Home > Conditional Formatting > Highlight Cell Rules > A Date Occurring.

Visual example of selecting Conditional Formatting, hovering over Highlight Cells Rules, and selecting A Date Occurring.

You can select the following date options, ranging from yesterday to next month:

DATE function

Learn more 
Select from a range of dates, including yesterday, today, tomorrow, in the last 7 days, last week, and this week.

These 10 date options generate rules based on the current date. If you need to create rules for other dates (e.g., greater than a month from the current date), you can create your own new rule.

Below are step-by-step instructions for a few of my favorite conditional formats for dates.

Highlighting weekends

When you design an automated calendar you don’t need to color the weekends yourself. With the conditional formatting tool, you can automatically change the colors of weekends by basing the format on the WEEKDAY function. Assume that you have the date table—a calendar without conditional formatting:

Highlighting weekends in Microsoft Excel example.

To change the color of the weekends, open the menu Conditional Formatting > New Rule.

Setting New Rule under Conditional Formatting.

In the next dialog box, select the menu Use a formula to determine which cell to format.

New Formatting Rule popup block. Select Use a formula to determine which cells to format and edit the Rule Description.

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

Parameter 2 means Saturday = 6 and Sunday = 7. This parameter is very useful to test for weekends.

Parameter added to section "Format values where this formula is true."

Note: In this case, you must lock the reference of the row so that the conditional format will work correctly in the other cells in this table.

Then, customize the format of your condition by clicking on the Format button and you choose a fill color (orange in this example).

Customize the format of your condition by clicking on the Format button.

format numbers as dates <br>or times in Excel

Learn more 

Click OK, then open Conditional Formatting> Manage Rules.

How to select Manage Rules under Conditional Formatting in Microsoft Excel.

Select This Worksheet to see the worksheet rules instead of the default selection. In Applies to, change the range that corresponds to your initial selection when creating your rules to extend it to the whole column.

Select This Worksheet to see the worksheet rules.

Now you will see a different color for the weekends. Note: This example shows the result in the Excel Web App.

Highlighting holidays

To enrich the previous workbook, you could also color-code holidays. To do that, you need a column with the holidays you’d like to highlight in your workbook (but not necessarily in the same sheet). In our example, we have US public holidays in column AH (as related to the year in cell B2).

United States holidays in 2023 formatted in Excel.

Again, open the menu Conditional Formatting > New Rule. In this case, we use the formula COUNTIF in order to count if the number of public holidays in the current month is greater than 1.

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

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.

Move public holiday rule to the top of the list.

This example in the Excel Web App shows the result. Change the value of the month and the year to see how the calendar has a different format.

Highlighting delays

In case we want to change the color of cells based on our approach on a date again, we will use conditional formatting to make it work for us.

In the following example, we show:

  • Yellow dates between 1 and 2 months.
  • Orange dates between 2 and 3 months.
  • Purple dates more than 3 months.

We then construct three rules of conditional formatting using the formula DATEDIF. Respectively for the three cases the following formulas:

=DATEDIF($B2,$E$2,”m”)>0

=DATEDIF($B2,$E$2,”m”)>1

=DATEDIF($B2,$E$2,”m”)>2

Construct three rules of conditional formatting using the formula DATEDIF.

In the Excel Web App, try changing some dates to experiment with the result.

Employees talking in a hallway.

Task management in Microsoft 365

Collaborate on shared Office documents, including Excel, Word, and PowerPoint.

Color scales

Rather than choose a different color set for each period in our timeframe, we will work with the option of color scales to color our cells.

First, go into a new column (column E) and calculate the difference in the number of days in a year again with the DATEDIF formula and the parameter “yd”.

=DATEDIF($D2,TODAY(),”yd”)

Then choose the menu Conditional Formatting> New Rule option Format all cells based on their value and choose the following options:

  • Scale = 3 colors
  • Minimum = 0 Green
  • Midpoint = 10 White
  • Maximum = 30 Blue
New Rule formatting all cells based on their values. Color gradient indicates cell values.

The result is a gradient color scale with nuances from green to white and through blue. The closer to 0 the more green, the closer to 10 the more white, and the closer to 30 the more blue. In the Excel app, try changing some dates to experiment with the result.

Learn more

Explore Microsoft Excel today.

Additional resources: