Changing Employee Files to expired after a certain amount of time has passed

Topic Labels: Formulas
1292 4
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Hi Everyone,

i’m loving Airtable but am still quite new to the formulas.
i am inputting dates of my staffs first aid and criminal record checks to expire after 3 and 5 years.

i’m hoping to have the date issued in on column and the next have active or expired… but dear god i cant figure it out!

anyone give me a hand here?
thanks in advance.

4 Replies 4

Welcome to the community, @Travis_Johnson2! :grinning_face_with_big_eyes: You need to use the DATEADD() function in your formula. If your date issued field is named {Date Issued}, a formula to add 3 years would look like this:

DATEADD({Date Issued}, 3, "years")

Does this get you in the direction you’d like to go?

it does, thanks @Justin_Barrett i was hoping to make the column say active or expired instead of give me the date.
do if the date is today in 3 years the column would automatically change to expired?

is that possible?

Sorry for the delay, and for not understanding more clearly what you wanted to accomplish.

Yes, that can be done, and there are multiple ways to do it. One is to compare TODAY() or NOW() against the added date from that first formula I gave you, and see if the new date is before now, returning an appropriate string depending on the result:

IF(DATEADD({Date 1}, 3, "years") < NOW(), "❌ Expired", "✅ Active")

Screen Shot 2020-04-15 at 11.49.19 PM

Another is to use DATETIME_DIFF() to calculate the difference in years between today and the relevant certification date. Just be careful about which comparison operator you use. If you simply check to see if the difference is greater than 3 years, the results will be off:

IF(DATETIME_DIFF(NOW(), {Date 1}, "years") > 3, "❌ Expired", "✅ Active")

Screen Shot 2020-04-15 at 11.35.52 PM

You will need to use >= for this to work correctly:

Screen Shot 2020-04-15 at 11.42.42 PM

There may be more options as well, but these are likely the easiest.

very much appreciated! i’m really enjoying this!