Previous Topic Next Topic

Conditional formatting based on specific expiration date

[Closed]
Report
-
 Blocked Profile -
Hello,

I have a spreadsheet that tracks training my employees take. The cells include the dates (mm/dd/yyyy) that the courses were taken. Most of the courses expire on December 31 each year, but one expires on September 30 each year. How can I create a conditional formatting rule for those that go beyond those dates? I am currently using the following rule: =TODAY()-365. It turns the cells red, but for courses that are one year old and not exactly expired yet.

Thanks!

2 replies

Answer 1 / 2

You do understand, every time you open the worksheet, it will always be 365 days behind. You have to code in the date.

Check this out though for when you get past the date counter problem:
https://ccm.net/forum/affich-910435-conditional-formating-formula-cell-text-change-color

Answer 2 / 2

I do understand that my current rule will not solve the problem with the expiration date. I would like to set up a rule that when the date goes beyond September 30, the cell turns red. For instance, if someone were to take a course on August 1, 2015 that is what would be entered into the cell. When we reach any day after September 30, it would turn red. I do not want the cell to turn red after August 1 like my current rule is set up for.

Is there a rule that can be set that says the cell should be turned red when the date in the cell goes beyond a specific month and day?

Thanks!
Report
Blocked Profile
Yes, calculate the date, then make the rule to compare with the date.
Ask a question