Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Apr 15, 2018 04:27 PM
Hi there ! :grinning:
I’m very very new here, on Airtable, so to begin gently, I’ve come up with the idea to build a DB regrouping all the info I have on the pets I lived with (dead and alive).
But I’ve got a DATETIME_DIFF()
formula difficulty.
On my base, I’ve created kind of a summary table with all the general info concerning the pets (Species, race, gender, etc…)
On this table, I’ve got a field Date of Birth (DoB) and a field Date of Death (DoD).
What I would like to do is to create a field that would give me the actual age of the pet (if still alive) or the age of the pet when it died, on same table, if possible :winking_face: .
Reading the help documentation and many topics here concerning the DATETIME_DIFF()
formula I found this :
DATETIME_DIFF(TODAY(),{DoB},’years’)
Which works perfectly for the actual age of the living pet.
And…
DATETIME_DIFF({DoD},{DoB},’years’)
Which works perfectly for the age of the pet when it died.
If I get it right, I would need to create a IF()
nested formula mixing both of these ones above, but after many tries I can’t find my way around this…
Could someone help me ? :slightly_smiling_face:
Thanks a lot, in advance :slightly_smiling_face:
(PS: Sorry for my English :blush: … It’s not my mother language :winking_face: )
Apr 21, 2018 05:53 AM
Ouch :grinning_face_with_sweat: ! My bad :grinning_face_with_sweat: !
I don’t know exactly how it happened :grinning_face_with_sweat: … But it happened :grinning_face_with_sweat: …
Correcting that missing comma mistake, I also found an “empty space” mistake (between 2 commas) re-writing that formula :grinning_face_with_sweat:
Thank you very much Sir ! :slightly_smiling_face: :thumbs_up:
Everything works again :slightly_smiling_face:
Apr 21, 2018 06:49 AM
I’m sorry to ask again :blush: … But I’ve got a tiny question in addition :blush:
Still concerning my “Authors” table…
After correcting my mistakes, I modified a little bit my formula so when an author of a quote doesn’t have a date of birth, the result is “N/A”.
IF(
{DoB}=BLANK(),'N/A',
IF(
{DoD}=BLANK(),
DATETIME_DIFF(TODAY(),{DoB},'years'),
DATETIME_DIFF({DoD},{DoB},'years')
))
The formula works :slightly_smiling_face: !
I just wanted to add “years” somewhere so when there is an age to calculate, I get something like “63 years”.
I’ve tried this :
IF(
{DoB}=BLANK(),'N/A',
IF(
{DoD}=BLANK(),
DATETIME_DIFF(TODAY(),{DoB},'years') & ' years ' &,
DATETIME_DIFF({DoD},{DoB},'years') & ' years ' &
))
And other placements of the “years” but I’m not able to find the right place in the formula, so it doesn’t work :neutral_face: …
Maybe I’m just not writing the “years” right :sweat: .
I have had several problems the past few days to add plain text in my formulas :sweat: .
Thanks for the help in advance again :slightly_smiling_face: :blush:
Apr 21, 2018 07:10 AM
Try
IF(
{DoB}=BLANK(),'N/A',
IF(
{DoD}=BLANK(),
DATETIME_DIFF(TODAY(),{DoB},'years'),
DATETIME_DIFF({DoD},{DoB},'years')
)&' years'
)
Apr 21, 2018 02:35 PM
Perfect ! :slightly_smiling_face: :thumbs_up:
It works ! :slightly_smiling_face:
Thank you very very much !!! :slightly_smiling_face:
Feb 06, 2024 08:31 AM - edited Feb 06, 2024 08:34 AM
This is awesome, @Andre_Zijlstra, @Julian_Kirkness & @Ptt_Pch ! 😉
I used this for actors and historical characters who... 😉 aren't 233 years old! 😉
I couldn't figure out a work around to parse between the living and dead (I'm sure there are ways I could've figured out 😉 ) but this is THE BOMB!
THANK YOU! 😉
BLESS YOU! 😉