Highlight Future Expiration Dates

[JwalkerOCS]

Hi there, 

I am surprised at how hard it is to find out how to highlight future expiration dates, as the dates are 30, 60, 90 days away.  I find the "Format Cells that contain a date occurring" completely useless for this spreadsheet as there is no way to customize the date.  I will do my best to describe what I need to do...

I have a spreadsheet that tracks our teams certifications and when they expire.  

I enter the expiration date into the cell that correlates to the certificate (rows) as well as the employee (column).

The dates entered into the cell can be more than 36 months in the future.

I need the cell to highlight Green if the expiration date is 90-61 days away from todays date

I need the cell to highlight Yellow if the expiration date is 60-31 days away from todays date

I need the cell to highlight Red if the expiration date is 30 days or less from todays date

Any dates more than 91 days away need to remail unfilled.

Please help!  If you know how I'd appreciate your time, or if you know of a great tutorial pop it down and THANK YOU!!!!

[#2]

@JwalkerOCS You can use conditional formatting (highlight the range - in the example below it is B2:K11) and just tweak the formula for each format rule.  It's important that the cell reference in the rule matches the upper left most cell in the "Applies to" box.  

Formula in the "Rule" box

=AND(B2-TODAY()>60,B2-TODAY()<=90)

Hope this helps. 

Dexter

[#3 · Accepted solution]

@JwalkerOCS 

=AND(B2>=TODAY()+61,B2<=TODAY()+90)

You can try this rule for conditional formatting (green).

=AND(B2>=TODAY()+31,B2<=TODAY()+60)

You can try this rule for conditional formatting (yellow).

=B2<=TODAY()+30

You can try this rule for conditional formatting (red).

=$B$2:$H$19

This is the "applies to" range in the example.

For 3 different colors you have to enter 3 rules for conditional formatting. The screenshot shows the rules and the "applies to" ranges for conditional formatting.

[#4]

Thank you so much! This looks like its working YAY! If I may ask one (hopefully) more question. The cells that contain no data, how do I keep them without any conditions?

[#5]

Thanks very much Dexter, I couldn't make this work for some reason, but it's definitely on this use not you! :)

[#6]

@JwalkerOCS 

Unfortunately i didn't take into account empty cells in my first suggestion.

=AND(B2<=TODAY()+90,B2>=TODAY()+61,B2<>"")

=AND(B2<=TODAY()+60,B2>=TODAY()+31,B2<>"")

=AND(B2<=TODAY()+30,B2<>"")

This requires an additional condition (B2<>"") in the rules for conditional formatting.

[#7]

Oh my goodness, thank you SO SO MUCH!

[#8]

You are welcome.