Conditional Formating change colour based on how many days left before cell Date

[natpolyc]

Hello!

I am using the forum some time now but just registered because I tried some suggestions already posted but no luck
I have a column in Excel 2013 with certificate Due Dates:

If the certificate overdue: Red
If the certificate expires in 90 days/ 3 months form today: Yellow (for those 3 months until expiry)
If the certificate expires in more than 90 days: Green (for those 9 months until expiry)

(I don't mind about the "=" in the dates can be in any of the above stages)

Maybe the issue is the order that I have place in Conditional Formatting although or the "Stop if true" I have tried several.

Thank you so much for your kind assistance.

Natalia

 

[revcanon]

Try these three rules applied to your whole range:


'For overdue:

=H2-today()<0  '(formatted to red)

'for 3 months to expiry:

=IF(AND((H2-TODAY())>0),((H2-TODAY())<91)) '(formatted to yellow)

'for more than 90 days:

=H2-TODAY()>91  '(formatted to green)

Of course... your cell reference is going to be different. Change H2 to the first date to be looked at in the range.

 

[natpolyc]

Try these three rules applied to your whole range:


'For overdue:

=H2-today()<0  '(formatted to red)

'for 3 months to expiry:

=IF(AND((H2-TODAY())>0),((H2-TODAY())<91)) '(formatted to yellow)

'for more than 90 days:

=H2-TODAY()>91  '(formatted to green)

Of course... your cell reference is going to be different. Change H2 to the first date to be looked at in the range.

Thank you very much for the prompt reply.
It worked. I changed the order of the conditions to check first the red, then the green and finally the yellow.

Thanks again!