Formula or Conditional Formatting for Expiration Date Tracker

Justin H.

Hello, I am creating a tracker to track annual and 2 year registrations. I am trying to figure out:

1. If the Registration frequency is annual, how to make the expiration date 1 year from the effective date.
2. If the expiration date is 30 days or less, turn the status cell yellow
3. If the expiration date is 31 days or more, turn the status cell green
4. If the expiration date is in the past, turn the status cell red

David Tutwiler

You are looking for 2 formulas. The first for setting your expiration date should be pretty easy. An IF formula will set your expiration date based on the Registration Frequency and the Effective Date. This formula would need to be set up as a column formula for the Expiration Date Column.

=IF([Registration Frequency]@row = "2 Year", [Effective Date]@row + 730, IF([Registration Frequency]@row = "Annual", [Effective Date]@row + 365))

The other formula for health status should look like the following and should be set up as a column formula in the Status column.

=IF([Expiration Date]@row < TODAY(), "Red", IF([Expiration Date]@row < TODAY(+31), "Yellow", "Green"))

Justin H.

Hey @David Tutwiler ,

Thank you very much for the assistance. For the expiration formula, it is just adding 365 or 730 to the end of the date.

I also did a test for the status (row 16) and it is showing as invalid operation.

David Tutwiler

Your Effective Date and Expiration Date columns need to be Date columns. If they are just Text/Number columns then all of the formulas just treat the dates as text and what I have offered will not work. Once you change them it should all start functioning as expected.

Justin H.

@David Tutwiler That worked!! Thanks again. One last thing, If I copy down the formula to the entire column, how do you keep the blank cells from showing "red"?

David Tutwiler

Two things. 1st, if you right click one of the cells with the formula at the bottom you should have the option to select Column Formula. This will make sure that every row gets the same formula without you having to copy down.

Next, you can modify the formula to exclude blanks like this:

=IF(ISBLANK([Expiration Date]@row), "", IF([Expiration Date]@row < TODAY(), "Red", IF([Expiration Date]@row < TODAY(+31), "Yellow", "Green")))