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.
+21Welcome 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?
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?
+21it 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")
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")
You will need to use >= for this to work correctly:
There may be more options as well, but these are likely the easiest.
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")
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")
You will need to use >= for this to work correctly:
There may be more options as well, but these are likely the easiest.
very much appreciated! i’m really enjoying this!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.