Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here
Jun 13, 2016 07:03 AM
DATETIME_DIFF([date1], [date2], ‘units’)
I saw this formula but how do I modify it to include my fields and a result in years for the units?
Jun 13, 2016 07:56 AM
Here’s my age formula: DATETIME_DIFF(TODAY(), Birthday, ‘years’)
I have a field named “Birthday” in my table that contains the birthdate.
Jun 14, 2016 01:05 PM
I use Tim’s method, understanding you get an “ERROR” if the birthday field is not filled in.
Feb 21, 2017 11:24 AM
What do I do If I need my output to be years and months?
Feb 21, 2017 01:40 PM
To get the age of a person in months:
DATETIME_DIFF(TODAY(), Birthday, 'months')
-> 346
Modifying the above to always give you the remainder when dividing by years, using MOD:
MOD(DATETIME_DIFF(TODAY(), Birthday, 'months'), 12)
-> 10
Finally, concatenating that with the age in years and adding units:
DATETIME_DIFF(TODAY(), Birthday, 'years') & ' years, ' & MOD(DATETIME_DIFF(TODAY(), Birthday, 'months'), 12) & ' months'
-> 28 years, 10 months
Jan 07, 2018 03:49 PM
For the formula to work for me, I needed to make: , Birthday, => ,{Birthday},
Thanks for the help.
Jan 11, 2018 09:58 AM
Trying to use your DATETIME_DIFF(TODAY(), Birthday, ‘years’)
Still getting that error!! Please help!
Jan 11, 2018 09:59 AM
Please send me the entire code formula! Thanks.
Jan 11, 2018 06:59 PM
DATETIME_DIFF(TODAY(), {Birthday}, ‘years’)
And {Birthday} is a field in my table, of course.
Hope that works for you.
Jan 11, 2018 08:04 PM
Still giving the formula error!
Jan 12, 2018 09:44 AM
Maybe there’s something wrong with the data in your Birthday field or there is no information in that field, yet.
Jan 13, 2018 05:09 PM
Check to make sure the single quotes around 'years'
are straight single quotes (''
) and not ‘pretty’ matching inverted comma-style single quotes (‘’). Sometimes when you copy-and-paste from the forum, you inadvertently pick up the latter, which will cause an error. Simply overwrite them using the single-quote key on your keyboard.
Feb 26, 2018 03:47 PM
Good thoughts, this fixed it for me!
Here’s my final formula:
DATETIME_DIFF(TODAY(),Birthday, ‘years’)
Jun 07, 2018 12:43 PM
Thanks for your response to the “Age” question.
How about instead of Years and Months, you want Years and a Decimal Equivalent for months.
For example exactly 12 years 6 months would simply be 12.5 years old. How to do that .5 calculation within the formula?
Thanks
Jun 07, 2018 01:22 PM
To build upon @Matt_Bush’s example, try
IF(birthday,
DATETIME_DIFF(
TODAY(),
birthday,
'years')&
RIGHT(
ROUND(
MOD(
DATETIME_DIFF(
TODAY(),
birthday,
'days'
),
365
)/365,
1
)&'',
2
)
That gives you YY.M
.
Jun 07, 2018 01:56 PM
I changed birthday to {Birthdate} and used your example, but it failed.
Birthdate is my Field Name
See below:
IF({Birthdate}, DATETIME_DIFF(TODAY(),{Birthdate}, ‘years’) & RIGHT(ROUND(MOD(DATETIME_DIFF(TODAY(),{Birthdate},‘days’), 365)/365, 1) &’’, 2)
Jun 07, 2018 02:35 PM
Oops, I must’ve dropped a parenthesis between testing the routine and formatting it to post.
Try this:
IF(
{Birthdate},
DATETIME_DIFF(
TODAY(),
{Birthdate},
'years'
)&RIGHT(
ROUND(
MOD(
DATETIME_DIFF(
TODAY(),
{Birthdate},
'days'
),
365
)/365,
1
)&'',
2
)
)
Jun 11, 2019 12:48 PM
Works PERFECTLY! Thanks Tim!
Dec 07, 2019 01:33 AM
I used this formula
=TONOW({DOB}, ‘years’)
Jan 22, 2020 08:36 AM
None of the options worked for me! I get the error sign. :frowning: