Conditional formatting dates

[ThedoreHolley]

I am trying to conditional format a column that has dates. (D-mmm-yy).
I want to highlight dates that are 18 months -23 months amber. And anything 24 or older red.

 

[ThedoreHolley]

Does it have with my date format is 1- May-22. d-mmm-yy

 

[etaf]

format should not matter unless text - in which would not be amber

the format is how a date looks - Excel just uses a number and fraction for time

right click and reformat as general and see if a number appears

i did ask 1st post

are they real dates

or as also suggested - share the file

change format to d-mmm-yy

condfrmt-etaf.xlsx
	A
1	DATES
2	11-Sep-22
3	23-Jul-22
4	3-Jun-22
5	14-Apr-22
6	23-Feb-22
7	4-Jan-22
8	15-Nov-21
9	26-Sep-21
10	7-Aug-21
11	18-Jun-21
12	29-Apr-21
13	10-Mar-21
14	19-Jan-21
15	30-Nov-20
16	11-Oct-20
17	22-Aug-20
18	3-Jul-20
19	14-May-20
20	25-Mar-20
21	4-Feb-20
22	16-Dec-19
23	27-Oct-19
24	7-Sep-19
25	19-Jul-19
26	30-May-19
27	10-Apr-19
28	19-Feb-19
29
30
Sheet1

Cell Formulas
Range	Formula
A3:A28	A3	=A2-50

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A32	Expression	=AND(A2<>"",A2<DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))	text	YES
A2:A29	Expression	=AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-18,DAY(TODAY())))	text	YES

Formatted as general

condfrmt-etaf.xlsx
	A
1	DATES
2	44815
3	44765
4	44715
5	44665
6	44615
7	44565
8	44515
9	44465
10	44415
11	44365
12	44315
13	44265
14	44215
15	44165
16	44115
17	44065
18	44015
19	43965
20	43915
21	43865
22	43815
23	43765
24	43715
25	43665
26	43615
27	43565
28	43515
29
30
31
Sheet1

Cell Formulas
Range	Formula
A3:A28	A3	=A2-50

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A32	Expression	=AND(A2<>"",A2<DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))	text	YES
A2:A29	Expression	=AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-18,DAY(TODAY())))	text	YES

 

[ThedoreHolley]

format should not matter unless text - in which would not be amber

the format is how a date looks - Excel just uses a number and fraction for time

right click and reformat as general and see if a number appears

i did ask 1st post


or as also suggested - share the file

change format to d-mmm-yy

condfrmt-etaf.xlsx
	A
1	DATES
2	11-Sep-22
3	23-Jul-22
4	3-Jun-22
5	14-Apr-22
6	23-Feb-22
7	4-Jan-22
8	15-Nov-21
9	26-Sep-21
10	7-Aug-21
11	18-Jun-21
12	29-Apr-21
13	10-Mar-21
14	19-Jan-21
15	30-Nov-20
16	11-Oct-20
17	22-Aug-20
18	3-Jul-20
19	14-May-20
20	25-Mar-20
21	4-Feb-20
22	16-Dec-19
23	27-Oct-19
24	7-Sep-19
25	19-Jul-19
26	30-May-19
27	10-Apr-19
28	19-Feb-19
29
30
Sheet1

Cell Formulas
Range	Formula
A3:A28	A3	=A2-50

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A32	Expression	=AND(A2<>"",A2<DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))	text	YES
A2:A29	Expression	=AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-18,DAY(TODAY())))	text	YES

Formatted as general

condfrmt-etaf.xlsx
	A
1	DATES
2	44815
3	44765
4	44715
5	44665
6	44615
7	44565
8	44515
9	44465
10	44415
11	44365
12	44315
13	44265
14	44215
15	44165
16	44115
17	44065
18	44015
19	43965
20	43915
21	43865
22	43815
23	43765
24	43715
25	43665
26	43615
27	43565
28	43515
29
30
31
Sheet1

Cell Formulas
Range	Formula
A3:A28	A3	=A2-50

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A32	Expression	=AND(A2<>"",A2<DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))	text	YES
A2:A29	Expression	=AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-18,DAY(TODAY())))	text	YES

I've tried multiple times and every time I add the amber it changes the RED as well.

 

[etaf]

i guess , as mentioned, best if i can see the spreadsheet - are you able to drop onto a share - does it have confidential info in ? if so maybe blank that out

 

[ThedoreHolley]

i guess , as mentioned, best if i can see the spreadsheet - are you able to drop onto a share - does it have confidential info in ? if so maybe blank that out

It has a lot of sensitive information on it that is why I just sent the picture of the column.

 

[etaf]

ok

so if you change the format of the column to general
are they all numbers - as i have shown

can you perhaps screen shot the conditional formatting rules - as i have done - in post 4

did you try the new amber formula, i posted earlier in post 9 yesterday

=AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-18,DAY(TODAY())), A2>=DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))

did you use the sample i posted on the share to se eif that worked on your excel version and looks the same

 

[ThedoreHolley]

ok

so if you change the format of the column to general
are they all numbers - as i have shown

can you perhaps screen shot the conditional formatting rules - as i have done - in post 4

did you try the new amber formula, i posted earlier in post 9 yesterday

=AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-18,DAY(TODAY())), A2>=DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))

did you use the sample i posted on the share to se eif that worked on your excel version and looks the same

I think it was more user error on my part. It us working now with the updated formula. Thank you for your patience and support.

 

[etaf]

you are welcome, if working ok with updated formula , then i suspect the order in conditional formatting was not as shown OR STOP IF TRUE not ticked for both

make sure you decide where to put the = sign , also as mentioned in post

<2yrs and >2yrs alone will miss out EXACTLY 2 years date - hence the = , just needs to be in the colour you want

 

[ThedoreHolley]

you are welcome, if working ok with updated formula , then i suspect the order in conditional formatting was not as shown OR STOP IF TRUE not ticked for both

make sure you decide where to put the = sign , also as mentioned in post

<2yrs and >2yrs alone will miss out EXACTLY 2 years date - hence the = , just needs to be in the colour you want

Got it. Thanks