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.

 

[etaf]

are they real dates
any way
conditional formatting rule

Assuming dates in column a row 2
in this order and stop if true
=AND(A2<>"",A2<DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))
format red

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

Book1
	A
1
2	9/11/22
3	7/23/22
4	6/3/22
5	4/14/22
6	2/23/22
7	1/4/22
8	11/15/21
9	9/26/21
10	8/7/21
11	6/18/21
12	4/29/21
13	3/10/21
14	1/19/21
15	11/30/20
16	10/11/20
17	8/22/20
18	7/3/20
19	5/14/20
20	3/25/20
21	2/4/20
22	12/16/19
23	10/27/19
24	9/7/19
25	7/19/19
26	5/30/19
27	4/10/19
28	2/19/19
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

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[ThedoreHolley]

Here is the data I am using. I used the formula send, but as you can see it's all amber.

 

[etaf]

ok, did you put them in the same order
and stop of true

 

[ThedoreHolley]

I did them in order and as soon as apply the 2nd rule it is overriding the 1st.

 

[ThedoreHolley]

ok, did you put them in the same order
and stop of true

I wonder if it's is because I need 18-23 to have the second rule.

 

[etaf]

STOP IF TRUE _ ticked ?

 

[ThedoreHolley]

STOP IF TRUE _ ticked ?

Yes

 

[etaf]

and red one first

for amber rule you could use this
=AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-18,DAY(TODAY())), A2>=DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))
so that it does not return TRUE for 2 years dates

as you cna see in column C - it will also be amber in the RED zone - hence why red is first and stop if true
column F has the new rule inof

C,D & F are not used - just for information

you may need an = or not depending on what you want to do if exactly 2 years or 18months - RED or YELLOW

I have added to a share - so you can open the spreadsheet

will only be on dropbox for a few days

condfrmt-etaf.xlsx

Shared with Dropbox

www.dropbox.com

Book1
	A	B	C	D	E	F
1	DATES		AMBER	RED		AMBER
2	9/11/22		FALSE	FALSE		FALSE
3	7/23/22		TRUE	FALSE		TRUE
4	6/3/22		TRUE	FALSE		TRUE
5	4/14/22		TRUE	FALSE		TRUE
6	2/23/22		TRUE	FALSE		TRUE
7	1/4/22		TRUE	TRUE		FALSE
8	11/15/21		TRUE	TRUE		FALSE
9	9/26/21		TRUE	TRUE		FALSE
10	8/7/21		TRUE	TRUE		FALSE
11	6/18/21		TRUE	TRUE		FALSE
12	4/29/21		TRUE	TRUE		FALSE
13	3/10/21		TRUE	TRUE		FALSE
14	1/19/21		TRUE	TRUE		FALSE
15	11/30/20		TRUE	TRUE		FALSE
16	10/11/20		TRUE	TRUE		FALSE
17	8/22/20		TRUE	TRUE		FALSE
18	7/3/20		TRUE	TRUE		FALSE
19	5/14/20		TRUE	TRUE		FALSE
20	3/25/20		TRUE	TRUE		FALSE
21	2/4/20		TRUE	TRUE		FALSE
22	12/16/19		TRUE	TRUE		FALSE
23	10/27/19		TRUE	TRUE		FALSE
24	9/7/19		TRUE	TRUE		FALSE
25	7/19/19		TRUE	TRUE		FALSE
26	5/30/19		TRUE	TRUE		FALSE
27	4/10/19		TRUE	TRUE		FALSE
28	2/19/19		TRUE	TRUE		FALSE
29
Sheet1

Cell Formulas
Range	Formula
C2:C28	C2	=AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-18,DAY(TODAY())))
D2:D28	D2	=AND(A2<>"",A2<DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))
A3:A28	A3	=A2-50
F2:F28	F2	=AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-18,DAY(TODAY())), A2>DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))

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]

and red one first

for amber rule you could use this
=AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-18,DAY(TODAY())), A2>=DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))
so that it does not return TRUE for 2 years dates

as you cna see in column C - it will also be amber in the RED zone - hence why red is first and stop if true
column F has the new rule inof

C,D & F are not used - just for information

you may need an = or not depending on what you want to do if exactly 2 years or 18months - RED or YELLOW

I have added to a share - so you can open the spreadsheet

will only be on dropbox for a few days

condfrmt-etaf.xlsx

Shared with Dropbox

www.dropbox.com

Book1
	A	B	C	D	E	F
1	DATES		AMBER	RED		AMBER
2	9/11/22		FALSE	FALSE		FALSE
3	7/23/22		TRUE	FALSE		TRUE
4	6/3/22		TRUE	FALSE		TRUE
5	4/14/22		TRUE	FALSE		TRUE
6	2/23/22		TRUE	FALSE		TRUE
7	1/4/22		TRUE	TRUE		FALSE
8	11/15/21		TRUE	TRUE		FALSE
9	9/26/21		TRUE	TRUE		FALSE
10	8/7/21		TRUE	TRUE		FALSE
11	6/18/21		TRUE	TRUE		FALSE
12	4/29/21		TRUE	TRUE		FALSE
13	3/10/21		TRUE	TRUE		FALSE
14	1/19/21		TRUE	TRUE		FALSE
15	11/30/20		TRUE	TRUE		FALSE
16	10/11/20		TRUE	TRUE		FALSE
17	8/22/20		TRUE	TRUE		FALSE
18	7/3/20		TRUE	TRUE		FALSE
19	5/14/20		TRUE	TRUE		FALSE
20	3/25/20		TRUE	TRUE		FALSE
21	2/4/20		TRUE	TRUE		FALSE
22	12/16/19		TRUE	TRUE		FALSE
23	10/27/19		TRUE	TRUE		FALSE
24	9/7/19		TRUE	TRUE		FALSE
25	7/19/19		TRUE	TRUE		FALSE
26	5/30/19		TRUE	TRUE		FALSE
27	4/10/19		TRUE	TRUE		FALSE
28	2/19/19		TRUE	TRUE		FALSE
29
Sheet1

Cell Formulas
Range	Formula
C2:C28	C2	=AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-18,DAY(TODAY())))
D2:D28	D2	=AND(A2<>"",A2<DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))
A3:A28	A3	=A2-50
F2:F28	F2	=AND(A2<>"",A2<DATE(YEAR(TODAY()),MONTH(TODAY())-18,DAY(TODAY())), A2>DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(TODAY())))

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

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