Skip to main content

Hi there ! 😀


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 ? 🙂


Thanks a lot, in advance 🙂


(PS: Sorry for my English 😊 … It’s not my mother language :winking_face: )

Hi @Ptt_Pch,


I think you’re aiming at this:



  • When the pet is alive you need: Today - DoB

  • When the pet died you need: DoD - DoB


That makes it easy:


IF(

{DoD}=Blank(),

DATETIME_DIFF(TODAY(),{DoB},’years’),

DATETIME_DIFF({DoD},{DoB},’years’)

)


Hi @Ptt_Pch,


I think you’re aiming at this:



  • When the pet is alive you need: Today - DoB

  • When the pet died you need: DoD - DoB


That makes it easy:


IF(

{DoD}=Blank(),

DATETIME_DIFF(TODAY(),{DoB},’years’),

DATETIME_DIFF({DoD},{DoB},’years’)

)


I just tried and that works perfectly ! 🙂

(Yes, I was exactly aiming at this :winking_face: )


I think I may have forget one or 2 comma during my tries.


Anyway, thank you very very much for your help ! 🙂 :thumbs_up:


I’ll play around with this one in my other bases 🙂

(That’s the best way to learn :winking_face: )


I just tried and that works perfectly ! 🙂

(Yes, I was exactly aiming at this :winking_face: )


I think I may have forget one or 2 comma during my tries.


Anyway, thank you very very much for your help ! 🙂 :thumbs_up:


I’ll play around with this one in my other bases 🙂

(That’s the best way to learn :winking_face: )


I’m glad it worked out fine for you! :grinning_face_with_smiling_eyes:


Hhhmmm 🤔


I’ve got another question though 😋


Is there a way to complete or adapt this formula…


IF(
{DoD}=Blank(),
DATETIME_DIFF(TODAY(),{DoB},’years’),
DATETIME_DIFF({DoD},{DoB},’years’)
)

… to get the results in “number of years / number of months” instead of only “number of years” ? 🤔


I’m pretty sure I’m missing something simple here 😊 but I can’t find what …


I must admit, I’m not very skilled yet to translate my thoughts into logical arguments, but it will come 🙂 , I just need the right trigger 🙂 :winking_face: .


Thanks a lot in advance again 🙂 !


Hhhmmm 🤔


I’ve got another question though 😋


Is there a way to complete or adapt this formula…


IF(
{DoD}=Blank(),
DATETIME_DIFF(TODAY(),{DoB},’years’),
DATETIME_DIFF({DoD},{DoB},’years’)
)

… to get the results in “number of years / number of months” instead of only “number of years” ? 🤔


I’m pretty sure I’m missing something simple here 😊 but I can’t find what …


I must admit, I’m not very skilled yet to translate my thoughts into logical arguments, but it will come 🙂 , I just need the right trigger 🙂 :winking_face: .


Thanks a lot in advance again 🙂 !


Add-on :


I tried to modify the ’years’ expression in the formula to get the results in “numbers of years / numbers of months” but I well, it simply didn’t work :roll_eyes: .


I thought about adding :


IF(DATETIME_DIFF(TODAY(),{DoB},’years’)<1,>something to get the age in months]


but I don’t know how to write the end of this and how to implement it :roll_eyes: …


If someone has an answer/idea, I’m all “eyes” 😊


Many thanks in advance 🙂


Hi @Ptt_Pch


This formula should do it (replace Today() with relevant date field):


INT(DATETIME_DIFF(TODAY(),{From Date}, ‘months’)/12) & “:” & (DATETIME_DIFF(TODAY(),{From Date}, ‘months’) - INT(DATETIME_DIFF(TODAY(),{From Date}, ‘months’)/12) *12)


Hope this helps!


Hi @Ptt_Pch


This formula should do it (replace Today() with relevant date field):


INT(DATETIME_DIFF(TODAY(),{From Date}, ‘months’)/12) & “:” & (DATETIME_DIFF(TODAY(),{From Date}, ‘months’) - INT(DATETIME_DIFF(TODAY(),{From Date}, ‘months’)/12) *12)


Hope this helps!


It helped :winking_face: ! Thanks :winking_face:

At least, it helped me to think outside of the box 🙂 , which helped me with a formula in another base I’ve created 🙂 .


Concerning this particular formula, I’ve found the answer in another post :winking_face:



I don’t know if someone else would be interested in the formula I used, but just in case, I’ll leave it here 😀 :winking_face: (we never know :winking_face: )


IF(
{DoD}=BLANK(),
DATETIME_DIFF(TODAY(), {DoB}, 'years') & ' years ' & MOD(DATETIME_DIFF(TODAY(), {DoB}, 'months'), 12) & ' months',
DATETIME_DIFF( {DoD}, {DoB}, 'years') & ' years ' & MOD(DATETIME_DIFF({DoD}, {DoB}, 'months'), 12) & ' months'
)

Many thanks for the help 🙂 :thumbs_up:


I wasn’t sure if I needed to create a new topic as it concerns kind of a different problem I’m encountering.

But, it is linked to same DATETIME_DIFF formula, so I thought it was better to post here :winking_face: .


So, to summarize :


Few days ago, I was searching a DATETIME_DIFF formula that would get me the age of my actual living pets and the age of the ones who died.


I created 2 date fields in my pets table to that purpose :




  • Date of Birth > {DoB}


  • Date of Death > {DoD}


I asked for help and thanks to the Airtable Community my problem was solved 🙂 !


BUT, I now got another problem in another DB with that exact same DATETIME_DIFF formula and I truly don’t understand why 😓 ,


So, the DB is meant to regroup all the quotes I like (coming from movies, TV Shows, books, songs, etc… ).

Because each quote (often) has an author, I created a table “Authors” where I can put all the informations I want concerning the authors (logical 😋 ) .


In that table I created, 2 date fields, identical to the ones in my pets table :




  • Date of Birth > {DoB}


  • Date of Death > {DoD}


The purpose is, again, to calculate the age of the author when he/she died or the actual age of the author if he/she is still alive.


I thought that was a no brainer and copied/pasted (I tried to write it in the “formula box” too instead of copying/pasting it) the first formula that worked for my pets (before using the MOD() formula (see above :winking_face: )) :roll_eyes: :


IF(
{DoD}=Blank(),
DATETIME_DIFF(TODAY(),{DoB},’years’),
DATETIME_DIFF({DoD},{DoB},’years’)
)

But, it didn’t work this time :face_with_raised_eyebrow: (this is a screenshot of a “test Author table”)


Did I do something wrong ? :face_with_raised_eyebrow:

If yes, can someone explain me what ? 🤔

Because, as far as I understand the formula, the logic behind it, to me, seems to be the same … :face_with_raised_eyebrow:


Many thanks in advance 😀


I wasn’t sure if I needed to create a new topic as it concerns kind of a different problem I’m encountering.

But, it is linked to same DATETIME_DIFF formula, so I thought it was better to post here :winking_face: .


So, to summarize :


Few days ago, I was searching a DATETIME_DIFF formula that would get me the age of my actual living pets and the age of the ones who died.


I created 2 date fields in my pets table to that purpose :




  • Date of Birth > {DoB}


  • Date of Death > {DoD}


I asked for help and thanks to the Airtable Community my problem was solved 🙂 !


BUT, I now got another problem in another DB with that exact same DATETIME_DIFF formula and I truly don’t understand why 😓 ,


So, the DB is meant to regroup all the quotes I like (coming from movies, TV Shows, books, songs, etc… ).

Because each quote (often) has an author, I created a table “Authors” where I can put all the informations I want concerning the authors (logical 😋 ) .


In that table I created, 2 date fields, identical to the ones in my pets table :




  • Date of Birth > {DoB}


  • Date of Death > {DoD}


The purpose is, again, to calculate the age of the author when he/she died or the actual age of the author if he/she is still alive.


I thought that was a no brainer and copied/pasted (I tried to write it in the “formula box” too instead of copying/pasting it) the first formula that worked for my pets (before using the MOD() formula (see above :winking_face: )) :roll_eyes: :


IF(
{DoD}=Blank(),
DATETIME_DIFF(TODAY(),{DoB},’years’),
DATETIME_DIFF({DoD},{DoB},’years’)
)

But, it didn’t work this time :face_with_raised_eyebrow: (this is a screenshot of a “test Author table”)


Did I do something wrong ? :face_with_raised_eyebrow:

If yes, can someone explain me what ? 🤔

Because, as far as I understand the formula, the logic behind it, to me, seems to be the same … :face_with_raised_eyebrow:


Many thanks in advance 😀


As far as I can tell there is a comma missing in the second line:

It now reads:

DATETIME_DIFF(TODAY(){DoB},‘years’),


but it should be:

DATETIME_DIFF(TODAY(),{DoB}, ‘years’),


That should solve it.


As far as I can tell there is a comma missing in the second line:

It now reads:

DATETIME_DIFF(TODAY(){DoB},‘years’),


but it should be:

DATETIME_DIFF(TODAY(),{DoB}, ‘years’),


That should solve it.


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 ! 🙂 :thumbs_up:

Everything works again 🙂


I’m sorry to ask again 😊 … But I’ve got a tiny question in addition 😊


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


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 😐

Maybe I’m just not writing the “years” right 😓 .

I have had several problems the past few days to add plain text in my formulas 😓 .


Thanks for the help in advance again 🙂 😊


I’m sorry to ask again 😊 … But I’ve got a tiny question in addition 😊


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


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 😐

Maybe I’m just not writing the “years” right 😓 .

I have had several problems the past few days to add plain text in my formulas 😓 .


Thanks for the help in advance again 🙂 😊


Try


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

Try


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

Perfect ! 🙂 :thumbs_up:

It works ! 🙂


Thank you very very much !!! 🙂


It helped :winking_face: ! Thanks :winking_face:

At least, it helped me to think outside of the box 🙂 , which helped me with a formula in another base I’ve created 🙂 .


Concerning this particular formula, I’ve found the answer in another post :winking_face:



I don’t know if someone else would be interested in the formula I used, but just in case, I’ll leave it here 😀 :winking_face: (we never know :winking_face: )


IF(
{DoD}=BLANK(),
DATETIME_DIFF(TODAY(), {DoB}, 'years') & ' years ' & MOD(DATETIME_DIFF(TODAY(), {DoB}, 'months'), 12) & ' months',
DATETIME_DIFF( {DoD}, {DoB}, 'years') & ' years ' & MOD(DATETIME_DIFF({DoD}, {DoB}, 'months'), 12) & ' months'
)

Many thanks for the help 🙂 :thumbs_up:


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


Reply