Date Highlight that will go over a year away

[afspeck37]

Good day everyone. First I want to say that I have been working on this for a while and have not online looked through this forum but several others as well as Excels own help file at no prevail. So I greatly appreciate your help.

To simplify things I will just use A1 as the cell I am trying to format.

The date I placed in cell A1 is June 2017 written as 6/1/2017. What I need to do is have that cell highlight in three different colors based off of where it is in accordance to TODAY. I have tried several formulas in the Conditional Formatting rules section but it will not work.

What I would like done is for the cell to highlight red when I open Excel when it is within 30 days, highlight yellow when I am within 60 days and highlight green when it is more than 60 days.

I am using Excel 365 by the way.


The formulas I have tried are:
=DATEDIF(TODAY(),$A1,"yd")<30
=IFDATE(TODAY()<30)
=AND($A1>0,$A1<TODAY()-30)


I greatly appreciate any and all help

 

[barry houdini]

Hello afspeck77, welcome to MrExcel

....and what should happen once the date is in the past? Should it remain red?

 

[afspeck37]

Hello afspeck77, welcome to MrExcel

....and what should happen once the date is in the past? Should it remain red?

It it will not go into the past. The dates are referring to time change parts on an aircraft. Once it comes up red we will know that it is time to change the part. So as soon as we change it we will then update it.


Thank you you for the quick reply.

 

[barry houdini]

Sorry, had some urgent tasks to attend to.....

for the difference in days between two dates you can just subtract, but you also need to check that the cell isn't blank to avoid formatting blank cells. So try these conditions for red, yellow and green respectively:

=AND(A1<>"",A1-TODAY()<=30)
=AND(A1<>"",A1-TODAY()<=60)
=ISNUMBER(A1)

the he order of the conditions is important because dates within 30 days will satisfy all 3 conditions, so the conditions need to be listed in that order so that only the correct formatting is applied. You can change the order afterwards but typically the conditions are listed with the latest one you added first, so if you add those in reverse order they should be correctly ordered

 

[afspeck37]

Thank you so much, that worked great and I was able to double check by changing my date on my computer and reopen. So just so I understand incase I run into this again in the future, it is an AND statement that is saying that if the days between the date in the cell and TODAY is less than OR equal to 30 the statement becomes true and therefore highlights. But I do not understand the ISNUMBER formula in this case. That is the one that I am highlighting green for over 60 days away. Everything works now, I just want to understand.

Thank you again for your help, is there any way to "upvote" you for your help?