How to make multiple "date" rules against "today" in a single column of data

Copper Contributor


I have to monitor many clients and be able to order the data based on when they were referred in from oldest to newest. But in addition, I would like to make them RED, AMBER or GREEN based on how old they are against the current date (ie today).


- More than 2 days old = GREEN

- More than 3 days old = AMBER

- More than 5 days old = RED

Can someone tell me the formula I should use, or how I can set the rules under conditional formatting?



1 Reply


Let's say the referral dates are in D2:D100.

Select this range, or if you want to color multiple columns, select the entire range that you want to color.

The active cell in the selection should be in row 2.


On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=AND($D2<>"", $D2<TODAY()-2)

Click Format...
Activate the Fill tab.
Select green as fill color.
Click OK, then click OK again.


Repeat, but with

=AND($D2<>"", $D2<TODAY()-3)

and amber as fill color.


Repeat again with

=AND($D2<>"", $D2<TODAY()-5)

and red as fill color