Conditional Formatting based on dates and days passed

[R4nd0m4ng3l]

Hi,

I am trying to add conditional formatting to my spreadsheet. The sheet records when I receive applications and how long since I received them. I have a column called 'Date Received' and want to add formatting so that the cell changes colour based on how long ago the date is. The parameters I need and below, any help would be great! Thanks in advance!

If the date is 0-28 days ago - Cell is Green
If the date is 29-42 days ago - Cell is Yellow
If the date if 43-56 days ago - Cell is Orange
If the date is over 57 days ago - Cell is Red

 

[Special-K99]

Try (untested)

4 Conditions
In this order

=(A1 < TODAY()-57)
format Red

=(AND(A1 < =TODAY()-43,A1 > =TODAY()-56))
format Orange

=(AND(A1 < =TODAY()-29,A1 > =TODAY()-42))
format Yellow

=(AND(A1 < =TODAY()-0,A1 > =TODAY()-28))
format Red

 

[R4nd0m4ng3l]

That doesn't seem to work either. Thanks for the suggestion though

 

[Special-K99]

Works fine for me.
Remove the spaces before/after the carat signs.

Clear any conditional formulas you have for A1
Select A1
Enter the 4 conditional formulas for A1
in A2 enter =TODAY()-A3

in A3 enter 58
A1 turns red its greater than 57 days

in A3 enter 44
A1 turns orange its between 43 and 56 days

in A3 enter 31
A1 turns yellow its between 29 and 42 days

in A3 enter 17
A1 turns green its between 0 and 28 days

 

[R4nd0m4ng3l]

I can get it to work on the one cell, is there a way I can then make it work for the whole column?

 

[Special-K99]

Use Format Painter (the paintbrush icon) to copy to other cells

 

[R4nd0m4ng3l]

Excellent! Thank you!