Expiration Date

[Jessika_Galvez]

Aloha!

We would like to have our dated fields have a formula for expiration date and for the color or something to change so it is obvious when things expire.

Does anyone know how to do this?

[AlliAlosa]

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")))

[Jessika_Galvez]

Can you help me? I am sorry to ask again… I understand what you are saying and that is exactly what I would like to do!

I would like the column to change color 1 month prior to the expiration date noted in the field. Are you able to help me and send me a code for 1 year expiration and then 2 year expiration by chance?

I am not sure how to make my own codes. lol

[Jessika_Galvez]

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

[AlliAlosa]

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”

[Jessika_Galvez]

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.

[AlliAlosa]

If you’re copying and pasting… this will only work if you have a field called {Revision Date}. You need to make sure that whatever is inside the curly brackets exactly matches the field name that you’re trying to reference :blush: