Use Custom Conditional Formatting Rules for Dates in Excel

01
of 02

Custom Conditional Formatting - Highlight Overdue Dates

Excel Conditional Formatting Date Formula
Excel Conditional Formatting Date Formula. Ted French

Conditional Formatting For Dates Overview

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.

To make using conditional formatting easier, since Excel 2007 there have been a number of pre-set options available that cover commonly used situations such as:

In the case of dates, the pre-set options make it easy to check 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, you can customize conditional formatting by adding your own formula using one or more of Excel's date functions.

Conditional Formatting Rules: Checking for Dates 30, 60, and 90 Days Past Due

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

The step by step example included with this tutorial sets three new conditional formatting rules that will check to see if the dates entered into a selected range of cells are:

  1. past 30 days
  2. past 60 days
  3. past 90 days

The formulas used in these rules subtract a certain number of days from the current date in cells C1 to C4.

The current date is calculated using the TODAY function.

For this tutorial to work you must enter dates that fall within the parameters listed above.

Note: Excel applies conditional formatting in the order - top to bottom - that the rules are listed in the Conditional Formatting Rules Manager dialog box as seen in the image above.

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

Adding the First Conditional Format Rule - Checking for Dates 30 days past due

  1. Highlight cells C1 to C4 to select them - this is the range to which we will apply the conditional formatting rules
  2. Click on the Home tab of the ribbon
  3. Click on the Conditional Formatting icon to open the drop-down menu
  4. Choose the New Rule option to open the New Formatting Rule dialog box
  5. Click on the Use a Formula to determine which cells to format option from the list at the top of the New Formatting Rule dialog box
  6. Enter the following formula into the box below the Format values where this value 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

  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 - to match the example in this tutorial, choose light green
  10. Click the Font tab to see font format options
  11. Under the color section, set the font color to white to match this tutorial
  12. Click OK twice to close the dialog box and return to the worksheet
  13. The background color of cells C1 to C4 will change to the fill color chosen - even though there is no data in the cells
02
of 02

Conditional Formatting For Dates Example Continued

Excel Conditional Formatting Date Formula
Excel Conditional Formatting Date Formula. Ted French

Entering the Next Two Conditional Format Rules Using 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 which will allow us to add the additional rules all at once.

Adding a Rule for Dates More Than 60 days Past Due

  1. Highlight cells C1 to C4 if necessary
  2. Click on the Home tab of the ribbon
  3. Click on the Conditional Formatting icon to open the drop-down menu
  4. Choose the Manage Rules option to open the Conditional Formatting Rules Manager dialog box
  5. Click on the New Rule option in the top left corner of the dialog box
  6. Click on the Use a Formula to determine which cells to format option from the list at the top of the dialog box
  7. Enter the following formula into the box below the Format values where this value 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

  8. Click the Format button to open the Format Cells dialog box
  9. Click the Fill tab to see the background fill color options
  10. Select a background fill color - to match the example in this tutorial, choose yellow
  11. Click OK twice to close the dialog box and return to the Conditional Formatting Rules Manager dialog box

Adding a Rule for Dates More Than 90 days Past Due

  1. Repeat steps 5 to 7 above to add a new rule
  2. For the formula use

    =TODAY()-C1>90

  3. Select a background fill color - to match the example in this tutorial, choose orange
  4. Set the font color to white to match this tutorial
  5. Click OK twice to close the dialog box and return to the Conditional Formatting Rules Manager dialog box
  6. Click OK again to close this dialog box and return to the worksheet
  7. The background color of cells C1 to C4 will change to the last fill color chosen

Testing the Conditional Formatting Rules

As can be seen in the image above, we can 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
  • (Note: If you are having trouble calculating such a date, as seen in the image above, the formula =TODAY() - 40 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 white text
  • 100 days before the current date - the cell should change to a dark red background with white text

Alternative Conditional Formatting Rules

If your worksheet already displays the current date - and most worksheets do - an alternative formula to those above can use the cell reference to the cell where the current date is displayed rather than using the TODAY function.

For example, if the date is displayed in cell B4, the formula entered as the rule to conditionally format dates that are more than 30 days past due could be:

  =$B$4 > 30 

The dollar signs ( $ ) surrounding the cell reference B4 prevents the cell reference from changing if the conditional formatting rule is copied to other cells in the worksheet.

The dollar signs create what is known as an absolute cell reference.

If the dollar signs are omitted and the conditional formatting rule is copied, the destination cell or cells will most likely display an #REF! error message.

Format
mla apa chicago
Your Citation
French, Ted. "Use Custom Conditional Formatting Rules for Dates in Excel." ThoughtCo, Apr. 30, 2017, thoughtco.com/custom-conditional-formatting-rules-for-dates-3123335. French, Ted. (2017, April 30). Use Custom Conditional Formatting Rules for Dates in Excel. Retrieved from https://www.thoughtco.com/custom-conditional-formatting-rules-for-dates-3123335 French, Ted. "Use Custom Conditional Formatting Rules for Dates in Excel." ThoughtCo. https://www.thoughtco.com/custom-conditional-formatting-rules-for-dates-3123335 (accessed January 23, 2018).