Help

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

Re: DATETIME_DIFF() - Mixing up the age of living and dead “people”? [SOLVED]

1774 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Ptt_Pch
8 - Airtable Astronomer
8 - Airtable Astronomer

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

14 Replies 14

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:

Ptt_Pch
8 - Airtable Astronomer
8 - Airtable Astronomer

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:

Try

IF(
    {DoB}=BLANK(),'N/A', 
    IF(
        {DoD}=BLANK(),
        DATETIME_DIFF(TODAY(),{DoB},'years'),
        DATETIME_DIFF({DoD},{DoB},'years')
        )&' years'
    )

Perfect ! :slightly_smiling_face: :thumbs_up:
It works ! :slightly_smiling_face:

Thank you very very much !!! :slightly_smiling_face:

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! 😉