Sep 20 2022 12:08 PM
Sep 20 2022 12:08 PM
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!!!!
Sep 20 2022 12:24 PM
@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
Hope this helps.
Sep 20 2022 12:24 PMSolution
You can try this rule for conditional formatting (green).
You can try this rule for conditional formatting (yellow).
You can try this rule for conditional formatting (red).
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.
Sep 20 2022 12:56 PM
Sep 20 2022 12:57 PM
Sep 20 2022 01:18 PM
Unfortunately i didn't take into account empty cells in my first suggestion.
This requires an additional condition (B2<>"") in the rules for conditional formatting.