Conditional Formatting Question

[Vladogs]

Good day guys,

I have a bunch of dates in a column. For instance:

02/04/2023
12/10/2022
12/11/2022
12/12/2022
12/01/2023


These are inspection dates and they expire in six months. What I need to do is to highlight each cell Red when inspection is due within 30 days and yellow when its due in 60 days and green if it's due in longer than 2 months. How do I go about this please?

Thank you.

 

[kevin9999]

You haven't said what you wanted if the date has expired, also none of the dates you list are future dates. However, try this and see if it gives you what you want:

Book1
	A
1	10/05/2023
2	20/05/2023
3	30/05/2023
4	09/06/2023
5	19/06/2023
6	29/06/2023
7	09/07/2023
8	19/07/2023
9	29/07/2023
10	08/08/2023
11	18/08/2023
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A14	Expression	=AND(A1<>"",(A1-TODAY())<30)	text	YES
A1:A14	Expression	=AND(A1<>"",(A1-TODAY())<60)	text	YES
A1:A14	Expression	=AND(A1<>"",(A1-TODAY())>=60)	text	NO

 

[Vladogs]

Thank you for you reply. Let me clarify one more time:

I am going to have a column of inspection dates which all took place in the past. I need to conditionally format this column in such manner that any date that is 150 days or more from the cell date will be highlighted red, between 120-150 days to be highlighted yellow and anything up to 120 days from the date in the cell to be highlighted green.

 

[kevin9999]

I'm not following you 100%. When you say "any date" versus "cell date" - what are the two dates you're referring to? Could you provide a small sample of your data using the XL2BB add in that clearly indicates what the two dates are?

 

[Vladogs]

Inspection dates For ex. this is when the actual inspection took place.

01/01/2023 The inspection is valid for 6 months which is when it needs refreshing. From this cell we can see that next inspection is due 01/01/2023 +180 days. What I want to do is when the this date is approaching inspection to highlight the cell in Red when there is 30 days left before the inspection i.e. 01/01/2023+150 days, yellow when 60 days and green when more than 60 days

12/12/2022

01/12/2022

01/03/2023

07/04/2023

08/04/2023

07/12/2022

08/01/2023

 

[Vladogs]

Sorry that didn't copy across well.

List of inspection dates:

01/01/2023
01/12/2022
10/11/2022
07/04/2023

An inspection needs refreshing after 180 days from the inspection date in the column. What I need is that when it's 30 days or less before next inspection must take place, Cell highlights Red, Yellow if it's between 30 and 60 days. Anything that is beyond 60 days- green.

Hope it makes sense.

 

[kevin9999]

OK, I think I get what you're after. Try the following:

Book1
	A
1	01/01/2023
2	01/12/2022
3	10/11/2022
4	07/04/2023
5	12/12/2022
6	01/03/2023
7	09/07/2023
8
9
10
11
12
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A12	Expression	=AND(A1<>"",(A1+180)-TODAY()<=30)	text	YES
A1:A12	Expression	=AND(A1<>"",(A1+180)-TODAY()<=60)	text	YES
A1:A12	Expression	=AND(A1<>"",(A1+180)-TODAY()>60)	text	NO

 

[Vladogs]

Thank you very much, we are getting somewhere for sure. Only one problem now, it doesn't highlight the cell Red for the first condition. Can you see if I am doing something wrong on my sample here:

 

[Vladogs]

Actually, I think I had the rules in the wrong order, that has worked now!

 

[kevin9999]

Thank you very much, we are getting somewhere for sure. Only one problem now, it doesn't highlight the cell Red for the first condition. Can you see if I am doing something wrong on my sample here:

View attachment 91127

You need to change the order of the conditions. Note how in post #7 I have the <=30 condition in first place, you have it in third place. And as I'm typing this I see you've figured it out already