Traffic lights for Excel

[LorraineR]

I wonder if you can assist a dinosaur with this please.
I am trying to input a traffic light system onto a spreadsheet where the green light will change to amber when the date is almost expired ie 14days, and the amber chances to red when it has expired.

any Formula I use the reaffirmation lights all go to green. Can you help?

ie
16/04/20 -require this to be amber
16/0320 - require this to be red
16/05/20 - require this to be green.

thank you so much
Lorraine

 

[etaf]

assuming the date is in column A
i have also added the formulas in columns B,C,D - so you can see the results, you see RED & Amber is both true - BUT the order you add to conditional formatting and stop if true will sort that out

ConFrmt-etaf.xlsx
	A	B	C	D	E	F	G	H
1		Green	AMBER	RED	16/04/20 -require this to be amber
2	4/16/20	FALSE	TRUE	FALSE	16/0320 - require this to be red
3	3/16/20	FALSE	TRUE	TRUE	16/05/20 - require this to be green.
4	5/16/20	TRUE	FALSE	FALSE
5					I am trying to input a traffic light system onto a spreadsheet where the green light will change to amber when the date is almost expired ie 14days, and the amber chances to red when it has expired.
6
7
8
9
10
11
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=A2>DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())+14)
C2:C4	C2	=A2<DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())+14)
D2:D4	D2	=A2<TODAY()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=AND(A1<>"",A1<TODAY())	text	NO
A:A	Expression	=AND(A1<>"",A1<DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())+14))	text	YES
A:A	Expression	=A1>DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())+14)	text	YES