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

 

[etaf]

you probably need STOP IF TRUE set
other wise anything less than or = 5 years will be green

what is green ay way , if = 5 years
in which case
=DATE(YEAR(B2),MONTH(B2),DAY(Q2)=YEAR(TODAY())-5)

formula wrong
=DATE(YEAR(B2),MONTH(B2),DAY(Q2))=YEAR(TODAY())-5)
and
=DATE(YEAR(B2),MONTH(B2),DayQ2)<YEAR(TODAY())-5
should be
=DATE(YEAR(B2),MONTH(B2),Day(Q2))<YEAR(TODAY())-5

also NO Q column in example

should that be Q or also B2????

 

[Nlhicks]

So it needs to be redone every 5 years. I am trying to signal to the person using the template that if it is in the yellow then it should be on the schedule for the next year and if it is red, they missed the deadline. I wouldn't mind coloring the whole line if that would make things simpler but I thought just coloring the due date would be okay.

 

[Nlhicks]

Arc Flash Tracking 2.xlsm
	A	B	C	D	E	F
1	Substation	Date Stickered	Stickered By	Due Date
2	Belfield	Aug-11		Jul-16
3	Bisbee	Oct-23	T. McCready	Sep-28
4	Bismarck	Oct-22	T. McCready	Sep-27
5	Cambell County			Dec-04
6	Carrington			Dec-04
NDMO

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

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D2	Expression	=DATE(YEAR(B2),MONTH(B2),DAY(Q2)=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:A6	List	=MyList2

 

[etaf]

see more comments in my first post

the formulas are wrong , missing brackets
and
Q column is that correct

to format the whole row - you need to select the range
and then use a $ in the columns for conditional formatting

Yellow ?
I only see red and green

sorry
can you manually enter the colours you want on the sheet and also - why they are that colour , then i can help make a formatting rule for that

 

[Nlhicks]

I am only using the Q column because the DATE Formula requires a Day. I don't need to use a Day only Month and Year

 

[Nlhicks]

see more comments in my first post

the formulas are wrong , missing brackets
and
Q column is that correct

to format the whole row - you need to select the range
and then use a $ in the columns for conditional formatting

Yellow ?
I only see red and green

sorry
can you manually enter the colours you want on the sheet and also - why they are that colour , then i can help make a formatting rule for that

I did not get the yellow to work

 

[etaf]

what are the conditions for
red
yellow
green
Date based on what column ?

i dont know whats in column Q - but if its the first , you can just put 1

=DATE(YEAR(B2),MONTH(B2),1)=YEAR(TODAY())-5)

as i mentioned
=DATE(YEAR(B2),MONTH(B2),DAY(Q2)=YEAR(TODAY())-5)
should be
=DATE(YEAR(B2),MONTH(B2),DAY(Q2))=YEAR(TODAY())-5)
and
=DATE(YEAR(B2),MONTH(B2),DAY(Q2))<=YEAR(TODAY())-5)

BUT you have = 5years
AND
<= 5years
so its always going to be true for the last formula and GREEN

so i dont know what you want for red, yellow , green

you also also comparing full date with year

i'm really confused sorry

 

[Nlhicks]

This is what I am looking for but I want the formulas to do it for me:

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	Aug-11		Aug-16			Could not label due to Construction project blocking access, still needs to be done
3	Bisbee	Oct-19	T. McCready	Sep-24
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
6	Carrington
7	Custer Trail
8	Denbeigh
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
NDMO

Cell Formulas
Range	Formula
D2:D4	D2	=DATE(YEAR(B2)+$P$2,MONTH(B2),DAY(Q2))
D5:D9	D5	=IF(ISBLANK(B5),"",DATE(YEAR(B5)+$P$2,MONTH(B5),DAY(Q5)))

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

 

[etaf]

Ok
so
as asked what are the rules for the colours and what date are we using ?

column D Due date
and
Red if 5 yrs out,
Yellow if 4 yrs out
and green otherwise

you have not include column P - so the XL2BB does not work when i copy it in to a spreadsheet

its maybe me, but i really am having trouble with your answers

can you explain in detail - WHY those colours

i dont know if we are comparing today dates or the difference in B and D

hopefully another member may pick up and understand , but i'm NOT - sorry