Conditional Format to turn Red if 5 yrs out, Yellow if 4 yrs out and green otherwise

[Nlhicks]

With this formatting, I get the Green to show up but when I change the date in column B to Aug-2011, the red does not work and when I change it to the formula for yellow, all the cells change regardless of what one I am working on.

Arc Flash Tracking 2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Substation	Date Stickered	Stickered By	Due Date			Field Comments
2	Belfield			Dec-04			Could not label due to Construction project blocking access, still needs to be done
3	Bisbee	Oct-23	T. McCready	Sep-28
4	Bismarck	Oct-22	T. McCready	Sep-27			Model is 99% done, waiting on Fuse verification from NDMO, have email string in folder, needs labeled still
5	Cambell County			Dec-04
6	Carrington			Dec-04
7	Custer Trail			Dec-04
8	Denbeigh			Dec-04
9	Devaul	Oct-22	T. McCready	Sep-27			New Station service transformer to be install poss. 2024/25, new xfmr info is in folder in email string. No cell service at sub, very remote, verify location prior to driving out
10	Devils Lake	Aug-22	T. McCready	Jul-27
11	Edgeley	May-23	T. McCready	Apr-28
12	Elliot			Dec-04
13	Fargo	May-23	T. McCready	Apr-28
NDMO

Cell Formulas
Range	Formula
D2:D13	D2	=DATE(YEAR(B2)+$P$2,MONTH(B2),DAY(Q2))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D33	Expression	=DATE(YEAR(B2),MONTH(B2),DayQ2)<YEAR(TODAY())-5	text	NO
D2:D33	Expression	=DATE(YEAR(B2),MONTH(B2),DAY(Q2)<=YEAR(TODAY())-5)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A2:A13	List	=MyList2

 

[Nlhicks]

Ohhh, okay column P is 5

 

[Nlhicks]

Ohhh, okay column P is 5

Here is what I need: If the due date is past the 5 year mark then obviously it is red.
If the due date is within the year then it is yellow
otherwise it is green.

 

[etaf]

ok,
so no colour between > 1 year and less than 5

 

[etaf]

colour all the cells GREEN - so that will cover every thing else

then RED use
=AND(D3<>"",D3<=DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY())))
then yellow use
=AND(D3<>"",D3>DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),D2<TODAY())

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Substation	Date Stickered	Stickered By	Due Date			Field Comments									over 5	less than 1
2	Belfield						Could not label due to Construction project blocking access, still needs to be done									TRUE	FALSE
3	Bisbee	45200	T. McCready	1/1/04												TRUE	FALSE
4	Bismarck	44835	T. McCready	5/30/06			Model is 99% done, waiting on Fuse verification from NDMO, have email string in folder, needs labeled still									TRUE	FALSE
5	Cambell County			10/26/08												TRUE	FALSE
6	Carrington			3/25/11												TRUE	FALSE
7	Custer Trail			8/21/13												TRUE	FALSE
8	Denbeigh			1/18/16												TRUE	FALSE
9	Devaul	44835	T. McCready	6/16/18			New Station service transformer to be install poss. 2024/25, new xfmr info is in folder in email string. No cell service at sub, very remote, verify location prior to driving out									FALSE	FALSE
10	Devils Lake	44774	T. McCready	11/12/20												FALSE	FALSE
11	Edgeley	45047	T. McCready	4/11/23												FALSE	TRUE
12	Elliot			9/7/25												FALSE	FALSE
13	Fargo	45047	T. McCready	2/4/28												FALSE	FALSE
14				7/3/30												FALSE	FALSE
15				11/29/32												FALSE	FALSE
16				4/28/35												FALSE	FALSE
17				9/24/37												FALSE	FALSE
18				2/21/40												FALSE	FALSE
19				7/20/42												FALSE	FALSE
20				12/16/44												FALSE	FALSE
21				5/15/47												FALSE	FALSE
22				10/11/49												FALSE	FALSE
23																FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
P2:P23	P2	=AND(D3<>"",D3<=DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY())))
Q2:Q23	Q2	=AND(D3<>"",D3>DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),D2<TODAY())
D4:D22	D4	=D3+880

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:D22	Expression	=AND(D3<>"",D3>DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),D2<TODAY())	text	NO
D3:D22	Expression	=AND(D3<>"",D3<=DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY())))	text	NO