# Conditional Formatting - Using Deadline Dates

By: mmcconaghy
October 4, 2013

We have had several requests for Conditional Formatting that colors a cell or range of cells as a deadline approaches.

This is for Excel 2007:

A                B              C
1) Name          Begin Date      Deadline Date:
2) Corley         04-Sep-13        03-Mar-14

3) Lindsey        04-Apr-13        01-Oct-13
5) Evans          22-Feb-13        21-Aug-13

We will be using Column C, the Deadline 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:

5 Days =  Red
15 Days =  Orange
30 Days =  Yellow
60 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: =C2-TODAY()<60

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: =C2-TODAY()<30

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: =C2-TODAY()<15

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: =C2-TODAY()<5

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 -  =C2-TODAY()<90
For 10 Days -  =C2-TODAY()<10

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?