Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Expiration Date

5757 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Jessika_Galvez
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

6 Replies 6
AlliAlosa
10 - Mercury
10 - Mercury

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

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

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.

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: