Conditional formatting dates, need upcoming due date and past due have read all threads and nothing is quite right

[kriscoon]

Hi everyone. I know there a ton of posts about conditional formatting for due dates, but I have read through almost all of them and I can't find anything that is quite right for my problem. I have a column of dates that have all happened years in the past. It was when our employees last had their medical fit test done. They need to have them every two years. What I am trying to accomplish is conditionally format the column so that when it gets to the point that it is about 60 days away from the time they need to get their next physical, I want it to highlight orange, at the 30 day mark I want it to go yellow. When it completely past the 2 year mark, I want it to turn red.

Help me Mr. Excel, you're my only hope

This is what the column of dates looks like. I put the colors next to the dates that I want to be able to format just so you could see what I am talking about. Hopefully this makes sense.
Red because it is already past 2 years
Yellow because it is within the 30 days before the 2 year mark
Orange because it is withing the 60 days before the 2 year mark

Does this make sense? (Oh, and this is all in a database so I don't have anywhere that I can put any other formulas or anything. I have to just be able to do it off of the column and nothing else)

Thank you in advance!

 

[DanteAmor]

Create the following 4 rules. Change B2 to the cell where your dates start.

Dante Amor
	B
1	Physical Exam
2	18/05/2022
3	15/12/2020
4	15/01/2021
5	07/02/2021
6
7
8
9
10
cf

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B10	Expression	=B2=""	text	YES
B2:B10	Expression	=DATE(YEAR(B2)+2,MONTH(B2),DAY(B2))<TODAY()	text	NO
B2:B10	Expression	=DATE(YEAR(B2)+2,MONTH(B2),DAY(B2))<TODAY()+30	text	NO
B2:B10	Expression	=DATE(YEAR(B2)+2,MONTH(B2),DAY(B2))<TODAY()+60	text	NO

 

[kriscoon]

Create the following 4 rules. Change B2 to the cell where your dates start.

Dante Amor
	B
1	Physical Exam
2	18/05/2022
3	15/12/2020
4	15/01/2021
5	07/02/2021
6
7
8
9
10
cf

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B10	Expression	=B2=""	text	YES
B2:B10	Expression	=DATE(YEAR(B2)+2,MONTH(B2),DAY(B2))<TODAY()	text	NO
B2:B10	Expression	=DATE(YEAR(B2)+2,MONTH(B2),DAY(B2))<TODAY()+30	text	NO
B2:B10	Expression	=DATE(YEAR(B2)+2,MONTH(B2),DAY(B2))<TODAY()+60	text	NO

Okay, first off @DanteAmor - You are my HERO!!! I have been working on this for over a week!! I started down the wrong path when I started using EDATE and couldn't get out of that rabbit hole. Now I can kind of see what the expression is doing, but would you mind breaking it down for me so that I can understand it and be able to use it better in the future?
Again, THANK YOU!!

 

[DanteAmor]

=DATE(YEAR(B2)+2,MONTH(B2),DAY(B2))<TODAY()

To the date that is in cell B2, with the date function, I am adding 2 years. As you can see I take the year of B2 and add 2 years to it. I keep the same month and the same day.
Then compare if this new date is less than today, it means that 2 years have already passed.

=DATE(YEAR(B2)+2,MONTH(B2),DAY(B2))<TODAY()+30

If the calculated date is less than today + 30 days, then it is in the yellow range.

 

[kriscoon]

To the date that is in cell B2, with the date function, I am adding 2 years. As you can see I take the year of B2 and add 2 years to it. I keep the same month and the same day.
Then compare if this new date is less than today, it means that 2 years have already passed.

If the calculated date is less than today + 30 days, then it is in the yellow range.

@DanteAmor, I seriously cannot thank you enough. That explanation has helped so much. I was attacking the problem from the complete wrong direction, and you really helped. Thank you!

 

[kriscoon]

@DanteAmor , So when I tried the formulas on a practice column, everything worked perfectly. But then when I went to my database to try it, I kept getting an error. Can I do this kind of formatting in a table?

This is the error that I keep getting:

Here is what I tried to type in the conditional formatting, =DATE(Year(X2)+2,MONTH(X2),DAY(X2))<TODAY()
My information is in column X
If you can help, that would be fantastic!

Thanks,

Kris

 

[DanteAmor]

=DATE(Year(X2)+2,MONTH(X2),DAY(X2))<TODAY()

Do you use comma ( , ) as argument separator or do you use semicolon ( ; ) ?
If you use semicolon ( ; ) try like this:

=DATE(Year(X2)+2;MONTH(X2);DAY(X2))<TODAY()