The problem

I recently had a plea for help from a client for whom I was running an Excel training course.

They wanted to highlight dates an Excel worksheet in various ways – red for dates in the past, amber for dates in the next week, yellow for dates a couple of weeks away, and green for dates further away than that.

This is an issue that will be familiar to anyone who deals with deadlines: orders due for shipping, service contracts due for renewal, milestones hurtling towards you in project management or exam dates if you’re a student.

The solution

Any time you want to highlight values in an Excel Worksheet based on criteria you set, you’ll be using the same tool – Conditional Formatting. You can find it on the Home tab of the Ribbon, about two-thirds of the way along:

Conditional formatting button in Excel

There are a number of ways you can approach this task – so on this page are just a few examples of how you can conditionally format dates in Excel.

To follow these examples refer to the workbook Conditional Format Dates.xlsx

Example 1. A simple way to highlight dates before or after today’s date

To conditionally format selected cells, use Excel’s built in Highlight Cell Rules with the =TODAY() function.

Spot dates in the future:

  1. Select the range of cells containing the dates you want to format
  2. On the Home ribbon click Highlight Cell Rules à Greater Than
  3. In the Greater than box enter =today()
  4. Optionally click the drop-down box ‘with’ and choose a different format
  5. Click OK

Spot dates in the past:

  1. Select the range of cells containing the dates you want to format
  2. On the Home ribbon click Highlight Cell Rules à Less Than
  3. In the Less than box enter =today()
  4. Optionally click the drop-down box ‘with’ and choose a different format
  5. Click OK

Note: The Excel TODAY function returns the current date, updated continuously when a worksheet is changed or opened. When you open the workbook in the future, the conditional formatting automatically updates. The screenshots in this example were taken on 8 June 2019 and you will get different results to those shown.

Example 2. A simple way to highlight dates using 10 built in options:

Excel provides 10 options to format selected cells based on the current date.

  1. Select the range of cells containing the dates you want to format
  2. On the Home ribbon click Highlight Cell Rules à A Date Occurring
  3. Select one of the date options from the drop-down list in the left-hand part of the window, ranging from last month to next month.
  4. Choose one of the pre-defined formats or set up your custom format by choosing different options on the Font, Border and Fill tabs, then click OK

Example 3. A simple way to highlight upcoming dates:

This example formats dates occurring in the next 14 days orange and also formats dates occurring in the next 30 days a yellow:

  1. Select the range of cells containing the dates you want to format
  2. On the Home ribbon click Highlight Cell Rules à Between
  3. In the first box type the formula =today()
  4. In the second box type the formula =today()+14
  5. Click the drop-down arrow in the with box and choose a custom format, then choose a fill colour of orange and click OK.
  6. Repeat the steps for the second date range: on the Home ribbon click Highlight Cell Rules à Between
  7. In the first box type the formula =today()+15
  8. In the second box type the formula =today()+30
  9. Click the drop-down arrow in the with box and choose a custom format, then choose a fill colour of yellow and click OK.

For more examples of how to use conditional formatting to highlight dates – including formatting an entire row – download the full guide as a PDF.