Conditional Format help

[Dclark015]

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]

I think you may need to change the "=" in your first rule to ">=" or "<=". Otherwise it will only work for that one day.
You may also need to change the order of your rules.

 

[GAJITCS]

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

 

[Dclark015]

Thanks for the replies- I've tried both suggestions and neither seemed to work. When adding the <= or >= it changes the fill color and does not change depending on what rule is on top. The same thing happens with Gajitcs formula.

My file has a section where gear expires after 10 years. If the gear was manufactured on 3/10/2013 then I want to fill the date as red and stay red for any days after that 10-year date. I also would like the date to turn Orange at the 9-year mark and then it would swap to red at 10 years. However any date before the 9-year mark I want it to remain non-filled.

An example is

Gear manufacture date 3/10/2023 Stays non-filled (White) until 3/10/2032 where it fills (orange) and then stays (orange) until 3/10/2033 where it fills (red) and stays (red) until deleted.

I hope that helps.

 

[Joe4]

Try this:

Those two formulas are:

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

and

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

 

[Dclark015]

Still not working, I did have to add a - to the rule for any color to change. Is there any chance both being same typre of time frame is whats causing it to not work?

 

[Joe4]

You seem to be flip/flopping on your date rules.
Are you trying to work with dates 10 years in the past or dates 10 years in the future?

You gave us an example with future 2033 dates:

Gear manufacture date 3/10/2023 Stays non-filled (White) until 3/10/2032 where it fills (orange) and then stays (orange) until 3/10/2033 where it fills (red) and stays (red) until deleted.

But in your last post you are showing old 2013 dates.

So which way are you really trying to go here???

 

[Dclark015]

You seem to be flip/flopping on your date rules.
Are you trying to work with dates 10 years in the past or dates 10 years in the future?

You gave us an example with future 2033 dates:


But in your last post you are showing old 2013 dates.

So which way are you really trying to go here???

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.

 

[GAJITCS]

Drop my fomulas into the worksheet and verify that you get the true and false values where you expect them.

 

[Joe4]

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:

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

and
Orange:

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

That seems to work for me.