Forum Discussion
Conditional Formatting Days since Date
I have a client spreadsheet and I am trying conditionally format the cells to highlight in different colours based on the number of days that have passed since the "Date of Last Contact".
Green: 0-3 days
Yellow: 4-9 days
Red: 10 days+
The formatting needs to correspond with the date in column J and most online tutorials use the =TODAY() function which does not quite suit my needs.
Can anybody please help me? 
Select the range that you want to format.
I will assume that the range starts in row 2. A cell in row 2 should be the active cell in the selected range.
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($J2<>"", $J2<=TODAY())
Click Format...
Activate the Fill tab.
Select green as fill color.
Click OK, then click OK again.Repeat these steps, but with the formula
=AND($J2<>"", $J2<=TODAY()-4)
and yellow as fill color.
Finally, repeat them again with
=AND($J2<>"", $J2<=TODAY()-10)
and red as fill color.
Thank you so much HansVogelaar!
