Expiration Date

Hi there! This is definitely possible… but the approach depends on how consistent your expiration dates are. Do you have a field for the expiration date that you update manually? Or do you want to do something like add a year to each revision date to get the next expiration date?

Let’s say you need to do the latter. I’d add a formula field, called {Expiration Date}, with a formula like…

IF({Revision Date}, DATEADD({Revision Date}, 1, 'year')

Then, you can use color conditions to highlight the rows where the {Expiration} date is coming up within however many days, weeks, months, etc. that you’d like.

OR you can add another field, called something like {Expiration Date Warning}, and use a formula like:

IF(IS_AFTER(TODAY(), {Expiration Date}), "‼️ Expired", IF(IS_SAME(TODAY(), {Expiration Date}), "📆 Expires Today", IF(IS_AFTER(TODAY(), DATEADD({Expiration Date}, -1, 'week')), "⚠️ Expires Soon")))

Hi there! This is definitely possible… but the approach depends on how consistent your expiration dates are. Do you have a field for the expiration date that you update manually? Or do you want to do something like add a year to each revision date to get the next expiration date?

Let’s say you need to do the latter. I’d add a formula field, called {Expiration Date}, with a formula like…

IF({Revision Date}, DATEADD({Revision Date}, 1, 'year')

Then, you can use color conditions to highlight the rows where the {Expiration} date is coming up within however many days, weeks, months, etc. that you’d like.

OR you can add another field, called something like {Expiration Date Warning}, and use a formula like:

IF(IS_AFTER(TODAY(), {Expiration Date}), "‼️ Expired", IF(IS_SAME(TODAY(), {Expiration Date}), "📆 Expires Today", IF(IS_AFTER(TODAY(), DATEADD({Expiration Date}, -1, 'week')), "⚠️ Expires Soon")))

Right now I am using a “date” field and just putting the expiration date in manually.

Sure thing! The first code I gave you will create an expiration date one year ahead of the last revision date.

IF({Revision Date}, DATEADD({Revision Date}, 1, 'year')

To add two years, just change the 1 to a 2.

IF({Revision Date}, DATEADD({Revision Date}, 2, 'year')

You can combine these formulas to just have one expiration date field… but what determines whether it’s one or two years ahead? For example, I have a file index base where I track my latest revisions of tons of documents. I have a field called {Check for Updates}, and I choose options like “Annually”, “As Needed”, “Semi-Annually”, etc. That field is then referenced to create my expiration date field.

To create a color condition… you’ll want to reference the {Expiration Date} field and use a condition like…

“Where {Expiration Date} is on or before one month from now”

I must be so dense when it comes to this. I copied and pasted the formulas you sent me but airtable said “invalid formula”. Do you know what I am doing wrong? Is there someone that I can speak with for a couple of minutes to help me with this? Sorry to be such a pain.