Change colour of cell based on time

[brodyfry]

Hi

I would like to setup a table so that when I input a date the colour of the cell will change to yellow 5 months from that date, then orange 5 months and 2 weeks from that date, then finally red 6 months from the inputted date

Thanks

 

[etaf]

setup 3 conditional format rules with 3 formulas and use stop if true
Put them in this order
then you can use
RED fill
=Cell with date <= Date(Year(Today()),Month(today())-6,day(today()))
Orange fill
=Cell with date <= Date(Year(Today()),Month(today())-5,day(today())-14)
Yellow fill
=Cell with date <= Date(Year(Today()),Month(today())-5,day(today()))

Book5
	A
1	Date
2	8/31/19
3	10/30/19
4	11/27/19
5	11/13/19
6
Sheet1

Cell Formulas
Range	Formula
A2	A2	=TODAY()-240
A3	A3	=TODAY()-180
A4	A4	=DATE(YEAR(TODAY()),MONTH(TODAY())-5,DAY(TODAY()))
A5	A5	=DATE(YEAR(TODAY()),MONTH(TODAY())-5,DAY(TODAY()-14))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A6	Expression	=AND(A2<>"",A2<=DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())))	text	YES
A2:A6	Expression	=AND(A2<>"",A2<=DATE(YEAR(TODAY()),MONTH(TODAY())-5,DAY(TODAY())-14))	text	YES
A2:A6	Expression	=AND(A2<>"",A2<=DATE(YEAR(TODAY()),MONTH(TODAY())-5,DAY(TODAY())))	text	YES