This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- What would be the formula to show the age of a per...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

0
15811
24

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

24 Replies 24

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 14, 2016 01:05 PM

I use Tim’s method, understanding you get an “ERROR” if the birthday field is not filled in.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 21, 2017 11:24 AM

What do I do If I need my output to be years and months?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 07, 2018 03:49 PM

For the formula to work for me, I needed to make: , Birthday, => ,{Birthday},

Thanks for the help.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 11, 2018 09:58 AM

Trying to use your DATETIME_DIFF(TODAY(), Birthday, ‘years’)

Still getting that error!! Please help!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 11, 2018 09:59 AM

Please send me the entire code formula! Thanks.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 11, 2018 08:04 PM

Still giving the formula error!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 12, 2018 09:44 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 13, 2018 05:09 PM

`'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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 26, 2018 03:47 PM

Good thoughts, this fixed it for me!

Here’s my final formula:

DATETIME_DIFF(TODAY(),Birthday, ‘years’)

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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`

.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 11, 2019 12:48 PM

Works PERFECTLY! Thanks Tim!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Dec 07, 2019 01:33 AM

I used this formula

=TONOW({DOB}, ‘years’)

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jan 22, 2020 08:36 AM

None of the options worked for me! I get the error sign. :frowning: