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: https://docs.google.com/spreadsheets/d/1VCpHj2tv8LEPea37E_I_7XcB3zPnudJTJ9L-gETZH3A/edit?usp=sharing

 

Thanks in advance

 

2 Replies

@DanielW1050 

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!