Forum Discussion
Solved
Highlight Future Expiration Dates
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" comp...
=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()+30You can try this rule for conditional formatting (red).
=$B$2:$H$19This 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.
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?
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.
- Oh my goodness, thank you SO SO MUCH!
- You are welcome.