Colouring cells to indicate how long ago a date is

New Contributor

Hello and thanks for reading my query,


I visit a lot of clients for work and I am trying to create a function in my date column that changes the colour of the cell under these conditions:

Green if the date is more recent than 30 days

Yellow if the date is between 30 and 60 days past

Red if it is greater than 60 days 


I have been messing around with the if function but i have not been able to combine these conditions.


Here is my sample google doc:


Thanks in advance


2 Replies


Delete the current rules.

Select C5:C75.


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 drop-down.

In the box next to it, enter =0

Click Format...

Activate the Fill tab.

Select red.

Click OK twice.


Repeat these steps, but with 'greater than or equal to', the formula =TODAY()-60 and yellow.

Finally, repeat them again with 'greater than', the formula =TODAY()-30 and green.


See the attached version.

Thank you very much!