Conditional Formatting

[Joleane V.]

Hello and Please Help!

I have been attempting to set up an excel sheet with conditional formatting to tell me when I have upcoming appointment dates. I would like the formats to include a 90 day / 60 day / 30 day warning and then the expiration date. I have tried multiple formulas and when I attempt to check the formula the rule doesn't work. I am unsure what I am doing wrong. PLEASE HELP!

The column that starts has the original date for the current annual physical - I need the formatting to tie into what I explained above.

- Jo

[#2]

Hi Joleane,

If A1 is the first cell of your range, formula for the first conditional formatting rule could be

=TODAY()>=A1

for the next rule

=TODAY()>=A1-30

etc

[#3]

Hello and Thank you for responding - I am attempting to do this for a full column and when I do it changes the entire column color. I included the document. I am extremely frustrated with this.

[#4]

All your dates in the sample are in the past - I added few future ones. Rules looks like this

and Green formula is

=(TODAY()>=B3-90)*NOT(ISBLANK(B3))

Compare to previous one I excluded blank cells from formatting. Rest is similar.

File attached.

[#5]

Thank you so much for taking some time to help me on this. However I think this is still not what I am going for.

I need the dates to be in the past and for the document to inform me of future upcoming dates. So for example:

The one annual was completed on the 7/10/2018 - when it gets to around April the document cell should change to Green for 90 days out from due date - then Blue for 60 days out in May - and then Yellow for 30 days out in June - by the time it is July 2019 the chart for that cell should turn Red indicating it is about to expire.

Does that make sense?

- Jo

[#6]

Joleane, let me clarify if I understood correctly

2018-07-10	Annual Physical
2019-07-10	Red starting from this date
2019-06-10	Yellow to above date
2019-05-11	Blue to above date
2019-04-11	Green to above date
	No format before above date

For that sample all dates are in future, thus it'll be no format for that cell for today.

[#7]

Yes that is what I needed!!!!

[#8]

So how does that actually happen?

[#9]

I'll update in some while, bit busy with another job

[#10]

You have been an absolute amazing bit of help - Thank you sooooooo much!!!!!

[#11]

No problem.

To modify the rule we add 12 months to the date in the table, the rest is the same. Like

=(TODAY()>=EDATE(B3,12)-90)*NOT(ISBLANK(B3))

Please check attached file.

[#12]

How did you get that to work!!!! I tried duplicating your formulas to see if it is just me and I cannot get them to go! You have been an amazing help.

Can you do that for every 2 years in the future and every ten?

[#13 · Accepted solution]

Joleane, 2 and 10 years, whatever, is exactly the same, you only need instead of EDATE(B3,12) which gives the same date in 12 months ahead, to use EDATE(B3,24), EDATE(B3,240), etc. And add more colors / rules for each such period.

[#14]

Sergei you have been an absolute savior for me today! Thank you so much for being so patient with me and helping me out!