Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Populating a field from one of two date fields using DATETIME_DIFF?

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

Hi, I have two date fields - one for a birthdate I actually know, and the other for an estimated birthdate (horse rescue here and sometime we know, sometimes we don’t…). I want to populate a third field to show “current age”. One or the other of the birthdate fields will be empty. I have the following but it doesn’t work. Can someone tell me how to fix it? Thanks for your help!

IF({Foal Date}, DATETIME_DIFF({Today},{Foal Date},‘years’), ELSE(DATETIME_DIFF( {Today},{Estimated Foal Date}, ‘years’))

4 Replies 4

ELSE() is not an Airtable function.

Your formula can be rewritten as

DATETIME_DIFF(
   {Today}, 
   IF({Foal Date}, {Foal Date}, {Estimated Foal Date}), 
   "years"
)

Thank you for your help. The revised formula gives me an age calculated from the Estimated Foal Date but not from the Foal Date

date

That’s because all your {Foal Date}s are empty and several of the {Estimated Foal Date}s are empty as well. If you want the formula to display as blank when neither field is filled in, you can revise the above formula like so:

IF(OR({Foal Date}, {Estimated Foal Date}), DATETIME_DIFF(
   {Today}, 
   IF({Foal Date}, {Foal Date}, {Estimated Foal Date}), 
   "years"
))

Perfect, Thank you so much! We are new to AT and have a huge job to import all of our data from several sources, some of which have no download ability so quite a lot of data entry. We have almost 700 horses entered into our base but in some cases we have very little info on them and age is often missing because they are feral horses or rescues from situations where their age is unknown. Eventually we will get them all at least an estimated foal date. Again, thank you from me, the keeper of the base, and the horses of Heart of Phoenix Equine Rescue.