Report

Conditional formatting based on specific expiration date

Ask a question Lisa - Latest answer on Sep 23, 2016 07:01PM
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!
See more 
Helpful
+0
moins plus
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:
http://ccm.net/forum/affich-910435-conditional-formating-formula-cell-text-change-color

Add comment
Helpful
+0
moins plus
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!
ac3mark 5861Posts Monday June 3, 2013Registration date ModeratorStatus September 26, 2016 Last seen - Sep 23, 2016 07:01PM
Yes, calculate the date, then make the rule to compare with the date.
Reply
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!