Conditional Formatting with Dates and coloring per rules

Copper Contributor

Hello I have a Spreadsheet with a set of people and for each of them columns with formulas that bring the date a visit should occur. That formula is a number of days after the first visit (over a period of years. Usually a visit every 4 months). I have to make sure that visit occurs withing +/-10 days. I want to have the cell be of a particular colour if the date projected is still more than 10 days aways, another color if it is between -10 and the expected visit date, to turn reddish if it is one to ten days pass, and strong red if it is more than 10 days overdue.

I tried setting up some rules but with no success.


R2 =SI($I2<>"";$I2+1016;"") where I2 is the date of the initial visit and Week 144 occurs 1016 days after (04/10/2023) then I want this to be light salmon colour.
If this was 25/11/2022 I want it let´s say orange.

If it was one day after (or up to ten days after), for example 15/11/2022 (yesterday being the ideal visit date) I want it light red

and if it was over ten days pass, let´s say 05/11/2022, strong red.


None of the rules I tried worked


Thank you!! 

1 Reply


Select the range that you want to format conditionally. I will assume that R2 is the active cell in the selection.

Set the fill color to strong red - this will be the default.


On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format only cells that contain'.

Leave the first drop down set to 'Cell Value'.

Select 'greater than' from the second dropdown.

In the box next to it, enter the formula =TODAY()-11

Use the version for your language - probably =HOY()-11
Click Format...
Activate the Fill tab.
Select light red.
Click OK, then click OK again.


Repeat these steps, but with =TODAY() and orange.

Finally, repeat them again with =TODAY()+10 and light salmon.