Help

Re: What would be the formula to show the age of a person as of today? I need the result in years

4049 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Sandra_McCann
4 - Data Explorer
4 - Data Explorer

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
Tim_Wilson
7 - App Architect
7 - App Architect

Here’s my age formula: DATETIME_DIFF(TODAY(), Birthday, ‘years’)

I have a field named “Birthday” in my table that contains the birthdate.

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

Sophie_Zifcak
4 - Data Explorer
4 - Data Explorer

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

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

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

Thanks for the help.

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

Still getting that error!! Please help!

Please send me the entire code formula! Thanks.

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

And {Birthday} is a field in my table, of course.

Hope that works for you.

Still giving the formula error!

Maybe there’s something wrong with the data in your Birthday field or there is no information in that field, yet.

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.

Good thoughts, this fixed it for me!

Here’s my final formula:

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

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

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.

David_Agresti
4 - Data Explorer
4 - Data Explorer

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)

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

Works PERFECTLY! Thanks Tim!

Junko_Ohara
4 - Data Explorer
4 - Data Explorer

I used this formula
=TONOW({DOB}, ‘years’)

Khatia_Odzelash
4 - Data Explorer
4 - Data Explorer

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

Screen Shot 2020-01-22 at 11.35.48 AM|429x500