Conditional Format help

Dclark015 · Mar 10, 2023

Good Morning All,

I have a sheet where I need to track 10 years after a date to show that it expires. Currently, I have 2 formulas (listed Below) however once it hits the 10-year mark it turns red and then the next day it goes back to the other format and turns back to orange 1 day after my end date. Again I'm sure its something simple that I am missing any help would be appreciated.

=AND(G2<>"",EDATE(G2,120)=TODAY()) This is first on the list and checked for "stop if True"

=AND(G2<>"",EDATE(G2,108)<=TODAY())

Thank you in advance,

Joe4 · Mar 10, 2023

Dclark015 said:
Sorry for the confusion. If what date frame they are matters for the formulas, then that's my misunderstanding. I am looking for a 10-period where gear goes bad. While inventorying the gear on hand some may have a manufacturing date from 2013 until 2023. So the formula would need to be in the past and the future I suppose because new dates would also be added when new gear is purchased.

OK, that was not really clear, that you needed to look both ways.

Try modifying the formulas I gave you like this then:
Red:

Excel Formula:

=AND(G2<>"",OR(G2>=EDATE(TODAY(),120),G2<=EDATE(TODAY(),-120)))

and
Orange:

Excel Formula:

=AND(G2<>"",OR(G2>=EDATE(TODAY(),108),G2<=EDATE(TODAY(),-108)))

That seems to work for me.

GAJITCS · Mar 10, 2023

Working perfectly well here.
When I enter 10/3/2013 in cell G2, the cell retains a default black on white format.
Change G2 to 11/3/2013 and the cell turns red.
Change G2 to 10/3/2014 and it stays red.
Change G2 to 11/3/2014 and it turns orange.

Joe4 · Mar 10, 2023

GAJITCS said:
Working perfectly well here.
When I enter 10/3/2013 in cell G2, the cell retains a default black on white format.
Change G2 to 11/3/2013 and the cell turns red.
Change G2 to 10/3/2014 and it stays red.
Change G2 to 11/3/2014 and it turns orange.

View attachment 87257

Gajitcs,

It sounds like they want it to work for BOTH 10 years in the past AND 10 years in the future.
Yours only works for the past dates, not also for the future dates.

Dclark015 · Mar 10, 2023

I really appreciate both of your help in this. I think I've confused everyone though. Forgetting the formula that I was using, which may be a part of the miscommunication.

I have a safety jacket that expires after 10 years from the manufacture date. We are looking for a way to have a color cue to know when its a year from expiration and then the day of expiration.

If the jacket was made on March 10, 2013 (The format in the spreadsheet is month Day Year) It then expires on March 10, 2023, 10 years after manufacture.

I am trying to change the color to orange at the 9-year mark from March 10, 2013. Which would be March 10, 2022

Then I am trying to change the color to red at the 10-year mark on March 10, 2023.

I want the color to stay orange for that entire 9th year, and then on year 10-anniversary turn red and remain red.

The color should remain white from the manufacture date until it turns orange.

Gen Otmin · Mar 11, 2023

I tried an IF and DATEDIF

Joe4 · Mar 11, 2023

Dclark015 said:
I really appreciate both of your help in this. I think I've confused everyone though. Forgetting the formula that I was using, which may be a part of the miscommunication.

I have a safety jacket that expires after 10 years from the manufacture date. We are looking for a way to have a color cue to know when its a year from expiration and then the day of expiration.

If the jacket was made on March 10, 2013 (The format in the spreadsheet is month Day Year) It then expires on March 10, 2023, 10 years after manufacture.

I am trying to change the color to orange at the 9-year mark from March 10, 2013. Which would be March 10, 2022

Then I am trying to change the color to red at the 10-year mark on March 10, 2023.

I want the color to stay orange for that entire 9th year, and then on year 10-anniversary turn red and remain red.

The color should remain white from the manufacture date until it turns orange.

Did you perhaps miss my reply and updated formulas in post 10 when this thread jumped to 2 pages?