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 15, 2018 10:25 PM
Hi @Ptt_Pch,
I think you’re aiming at this:
That makes it easy:
IF(
{DoD}=Blank(),
DATETIME_DIFF(TODAY(),{DoB},’years’),
DATETIME_DIFF({DoD},{DoB},’years’)
)
Apr 16, 2018 05:01 AM
I just tried and that works perfectly ! :slightly_smiling_face:
(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 ! :slightly_smiling_face: :thumbs_up:
I’ll play around with this one in my other bases :slightly_smiling_face:
(That’s the best way to learn :winking_face: )
Apr 16, 2018 05:24 AM
I’m glad it worked out fine for you! :grinning_face_with_smiling_eyes:
Apr 16, 2018 08:09 AM
Hhhmmm :thinking: …
I’ve got another question though :yum: …
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” ? :thinking:
I’m pretty sure I’m missing something simple here :blush: 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 :slightly_smiling_face: , I just need the right trigger :slightly_smiling_face: :winking_face: .
Thanks a lot in advance again :slightly_smiling_face: !
Apr 16, 2018 09:31 AM
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” :blush:
Many thanks in advance :slightly_smiling_face:
Apr 16, 2018 10:34 AM
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!
Apr 18, 2018 04:54 AM
It helped :winking_face: ! Thanks :winking_face:
At least, it helped me to think outside of the box :slightly_smiling_face: , which helped me with a formula in another base I’ve created :slightly_smiling_face: .
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 :grinning: :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 :slightly_smiling_face: :thumbs_up:
Apr 21, 2018 04:40 AM
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 :
{DoB}
{DoD}
I asked for help and thanks to the Airtable Community my problem was solved :slightly_smiling_face: !
BUT, I now got another problem in another DB with that exact same DATETIME_DIFF
formula and I truly don’t understand why :sweat: ,
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 :yum: ) .
In that table I created, 2 date fields, identical to the ones in my pets table :
{DoB}
{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 ? :thinking:
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 :grinning:
Apr 21, 2018 05:37 AM
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.