Conditional Formatting - Past Due Dates


By: mmcconaghy
October 16, 2013

We have had several requests for Conditional Formatting that colors a cell or range of cells for dates that are Past Due.

This is for Excel 2007:

With your data similar to:

      A                B    
1) Name            Due Dates:     
2) Corley          11-Oct-13     
3) Lindsey         06-Oct-13     
4) Adkisson        26-Sep-13     
5) Evans           16-Sep-13     


We will be using Column B, the Due Dates to determine which Names in Column A to highlight.

By changing which cells you select, you can highlight any of the columns you wish.

We will be using Four different colors, (see note at bottom) therefore, there are Four separate formulas that will be necessary. Additionally, they must be in the correct order, so after your done entering all the formulas they should be, in the following color order:

30 Days =  Red       
20 Days =  Orange 
10 Days =  Yellow  
 5 Days =  Green   


The reason for the correct order, is that we will be "cascading" down the formulas. CF requires only that a formula returns TRUE, so starting at the top, we test each formula, till one returns true, then display the corresponding color.

First Formula:

1) Select your cell or Range of Cells, A2 - A5 (Names)
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =TODAY()-B2>=5

6) Click on the Format button
7) Select the Fill Tab
8) Select a  Green  color
9) Click OK
10) Click OK

Second formula:

1) Select your cell or Range of Cells, should be the same as above.
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =TODAY()-B2>=10

6) Click on the Format button
7) Select the Fill Tab
8) Select a  Yellow  color
9) Click OK
10) Click OK

Third Formula:

1) Select your cell or Range of Cells, should be the same as above
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =TODAY()-B2>=20

6) Click on the Format button
7) Select the Fill Tab
8) Select a  Orange  color
9) Click OK
10) Click OK

Fourth Formula:

1) Select your cell or Range of Cells, should be the same as above
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =TODAY()-B2>=30

6) Click on the Format button
7) Select the Fill Tab
8) Select a  Red  color
9) Click OK
10) Click OK

As you can see, to change the length of time before a color is used you simply change the number of days at the end of the formula

For 90 Days -  =TODAY()-B2>=90
For 15 Days -  =TODAY()-B2>=15

NOTE:
In Excel 2007, conditional formatting can contain up to sixty-four conditions, but in earlier versions of Excel, only three conditions are supported.

When editing a Conditional Format formula, Excel will interpret the Arrow Keys as an attempt to point to a cell. This will of course change the formula. Hitting the Escape Key will return you to the original formula.


Need more help?
Describe your Problem
Example: Hard Drive Not Detected on My PC

Ask Question