How to Use Custom Conditional Formatting Rules for Dates in Excel

Woman typing on a computer with a spreadsheet on the screen

 

Manuel Breva Colmeiro/Getty Images

Adding conditional formatting to a cell in Excel allows you to apply different formatting options, such as color, when the data in that cell meets the conditions that you have set.

Instructions in this article apply to Excel 2019, 2016, 2013, 2010; Excel for Mac, Excel for Office 365, and Excel Online.

How to Use Conditional Formatting

To make conditional formatting easier, Excel supports pre-set options that cover commonly used situations, such as:

In the case of dates, the pre-set options simplify the process of checking your data for dates close to the current date such as yesterday, tomorrow, last week, or next month.

If you want to check for dates that fall outside of the listed options, however, customize the conditional formatting by adding your own formula using one or more of Excel's date functions.

01
of 05

Check for Dates 30, 60, and 90 Days Past Due

Excel cells with various dates in them.

In this demo, we're using the current date, 40 days before the current date, 70 days before the current date, and 100 days before the current date to generate the results. See step 5 for a quick way to generate these dates for this tutorial.

Customize conditional formatting using formulas by setting a new rule that Excel follows when evaluating the data in a cell.

Excel applies conditional formatting in top-to-bottom order as they appear in the Conditional Formatting Rules Manager dialog box.

Even though several rules may apply to some cells, the first rule that meets the condition is applied to the cells.

02
of 05

Check for Dates 30 Days Past Due

Excel creating a new conditional rule.

Using the data shown in the image above, highlight cells C1 to C4 to select them. This is the range to which the conditional formatting rules will be applied.

  1. Select Home > Conditional Formatting New Rule to open the New Formatting Rule dialog box.
  2. Click the Use a Formula to determine which cells to format option.
  3. Enter the following formula into the box below the Format values where this formula is true option in the bottom half of the dialog box:
    =TODAY()-C1 >30

    This formula checks to see if the dates in cells C1 to C4 are more than 30 days past.
  4. Click the Format button to open the Format Cells dialog box.
  5. Click the Fill tab to see the background fill color options.
  6. Select a background fill color.
  7. Click the Font tab to see font format options.
  8. Set the font color.
  9. Click OK twice to close the dialog box and return to the worksheet.
  10. The background color of cells C1 to C4 will change to the fill color chosen, even though there are no data in the cells.
03
of 05

Add a Rule for Dates More Than 60 days Past Due

Excel screenshot showing the Manage Rules option.

Rather than repeat all the steps above to add the next two rules, we will make use of the Manage Rules option that will allow us to add the additional rules all at once.

  1. Highlight cells C1 to C4, if necessary.
  2. Click Home > Conditional Formatting Manage Rules to open the Conditional Formatting Rules Manager dialog box.
  3. Click the New Rule option in the top left corner of the dialog box
  4. Click the Use a Formula to determine which cells to format option from the list at the top of the dialog box.
  5. Enter the following formula into the box below the Format values where this formula is true option in the bottom half of the dialog box:
    =TODAY()-C1>60
    This formula checks to see if the dates in cells C1 to C4 are greater than 60 days past.
  6. Click the Format button to open the Format Cells dialog box.
  7. Click the Fill tab to see the background fill color options.
  8. Select a background fill color.
  9. Click OK twice to close the dialog box and return to the Conditional Formatting Rules Manager dialog box.
04
of 05

Add a Rule for Dates More Than 90 days Past Due

Excel screenshot showing the options for a new rule.
  1. Highlight cells C1 to C4, if necessary.
  2. Click Home > Conditional Formatting Manage Rules to open the Conditional Formatting Rules Manager dialog box.
  3. Click the New Rule option in the top left corner of the dialog box
  4. Click the Use a Formula to determine which cells to format option from the list at the top of the dialog box.
  5. Enter the following formula into the box below the Format values where this formula is true option in the bottom half of the dialog box:
    =TODAY()-C1>90
  6. This formula checks to see if the dates in cells C1 to C4 are greater than 90 days past.
  7. Click the Format button to open the Format Cells dialog box.
  8. Click the Fill tab to see the background fill color options.
  9. Select a background fill color.
  10. Click OK twice to close the dialog box and return to the Conditional Formatting Rules Manager dialog box.
  11. Click OK again to close this dialog box and return to the worksheet.

The background color of cells C1 to C4 will change to the last fill color chosen.

05
of 05

Test the Conditional Formatting Rules

Excel Conditional Formatting Date Formula

Test the conditional formatting rules in cells C1 to C4 by entering the following dates:

  • The current date — the cell should change to the default white background with black text since none of the conditional formatting rules apply.
  • 40 days before the current date — the cell should change to a light green background with white text. If you use the following formula:
    =TODAY() - 40
    Excel will enter the date 40 days prior to the current date.​
  • 70 days before the current date — the cell should change to a yellow background with black text.
  • 100 days before the current date — the cell should change to a dark red background with white text.