# Calculate Age from birthdate

Options
edited 12/09/19

Is there a way to have a cell auto calculate age (years and months) from birth date in another cell?

Tags:
«13

• ✭✭✭✭✭✭
Options

You can use the netdays formula.

something like

=ROUND(NETDAYS(Birthdate@row, TODAY()) / 365 - 0.5) + " Years and " + ROUND((NETDAYS(Birthdate@row, TODAY()) / 365 - ROUND(NETDAYS(Birthdate@row, TODAY()) / 365 - 0.5)) * 12) + " Months"

Where the column with their birthdate is called "Birthdate"

• ✭✭✭✭✭✭
Options

This is might be correct mathematically, but is not correct for the way most people view their birthday.

Try Sep 20, 1963 (54 years old) to verify.

I assume that someone 29 years, 11 months, and 27 days will be classified at 29years, 11months. Correct me if I am wrong.

Craig

• ✭✭✭✭✭✭
Options

I think the main issue is leap years adding an extra day every 4 years. I didn't account for that , so for every 4 years older a person is their birthday will be a day closer.  That would be a pain to solve. I wish they would add netweek/netmonth/netyear formulas

• ✭✭✭✭✭✭
Options

There is a YEARDAY() formula, but it won't work because of leap years.

I have solved this before, but need to find it, it's buried on somewhere in my early Smartsheet days before I was organized*

Craig

*(that's partially sarcastic).

• ✭✭✭✭✭✭
Options

Luke,

Are you going to work on this? I won't if you are.

If you are use INT() instead of ROUND(..+0.5)

For Nicole, can the solution ignore people born on Feb 29th?

Craig

• ✭✭✭✭✭✭
Options

I'm not on this tonight.  I can check it out tomorrow and try to work something out.

I like these ones because I find they help me in knowing the complexity behind different solutions, so i'd definitely like to find an answer to it, but i'm heading out in just a few minutes

• ✭✭✭✭✭✭
Options

OK. I'm off for dinner but will be back later

Craig

• ✭✭✭✭✭✭
edited 09/19/18
Options

Cut 1:

=IF(MONTH(Birthday\$1) = MONTH(Birthday@row), IF(DAY(Birthday\$1) >= DAY(Birthday@row), YEAR(Birthday\$1) - YEAR(Birthday@row) + "Y 0M", YEAR(Birthday\$1) - YEAR(Birthday@row) - 1 + "Y 11M"), IF(MONTH(Birthday\$1) > MONTH(Birthday@row), YEAR(Birthday\$1) - YEAR(Birthday@row) + "Y " + IF(DAY(Birthday\$1) >= DAY(Birthday@row), MONTH(Birthday\$1) - MONTH(Birthday@row) + "M", MONTH(Birthday\$1) - MONTH(Birthday@row) - 1 + "M"), YEAR(Birthday\$1) - YEAR(Birthday@row) - 1 + "Y " + IF(DAY(Birthday\$1) > DAY(Birthday@row), MONTH(Birthday\$1) - MONTH(Birthday@row) + 1 + "M", 12 + (MONTH(Birthday\$1) - MONTH(Birthday@row)) + "M")))

Replace Birthday\$1 with TODAY(). I leave Birthday\$1 in so others can more easily test.

Rationale:

Various version of mathematics kept running into edge cases where it would be inaccurate.

Concept:

If birthday is this month then

if birthday day is today or in the past, happy birthday -- year(today)-year(birthday) and 0M

else birthday day is coming up so -- year(today)-year(birthday) - 1 and 11M

else

if month(today) > month(birthday) then

- birthday is coming up later in the year

else

- birthday happened this year

Assumptions:

Month counter increments on day of month (so if born on 10th of June, person is X years 0M until 10th of July)

The day before my birthday, I am X-1 years 11M.

Further thoughts:

This version does not need to account for leap year.

Formulas like

=INT(TODAY() - Birthday@row / 365.25)

do, and have slight error variations (when expanding to include month numbers too)

To be completely accurate, any formula MUST also account for DAY (born on 3/31/00, today is 4/1/00, need to be 0Y 0M (based on my assumption)

This is 546 characters -- too many for my taste.

Breaking into several columns might help.

I suspect rearranging the formula might allow shortening it, but I wanted to get this out there if someone wants to pick it apart.

I might be back with cut 2 later.

Craig

• ✭✭✭✭✭✭
Options

Cut 2:

Slightly shorter (467)

=IF(MONTH(Birthday\$1) = MONTH(Birthday@row), IF(DAY(Birthday\$1) >= DAY(Birthday@row), YEAR(Birthday\$1) - YEAR(Birthday@row) + "Y 0M", YEAR(Birthday\$1) - YEAR(Birthday@row) - 1 + "Y 11M"), IF(MONTH(Birthday\$1) > MONTH(Birthday@row), YEAR(Birthday\$1) - YEAR(Birthday@row) + "Y " + (MONTH(Birthday\$1) - MONTH(Birthday@row) + IF(DAY(Birthday\$1) >= DAY(Birthday@row), 0, -1)) + "M", YEAR(Birthday\$1) - YEAR(Birthday@row) - 1 + "Y " + (MONTH(Birthday\$1) - MONTH(Birthday@row) + IF(DAY(Birthday\$1) > DAY(Birthday@row), 1, 12)) + "M"))

I'm done.

Craig

• Options

column named DOB has date of birth

DOB = 3 Jan 1988

=YEAR(TODAY()) - YEAR(DOB1)

• ✭✭✭✭✭✭
Options

That will only work if someone's birthday is already past for the year. If their birthday is later in the year, then it will be one year more than their age. I would suggest taking a look at some of the other comments above for a more accurate solution.

• Options

Hi J. Craig Williams

=IF(MONTH(today() = MONTH([Column4]7), IF(DAY(today() >= DAY([Column4]7), YEAR(today() - YEAR([Column4]7) + "Y 0M", YEAR(today() - YEAR([Column4]7) - 1 + "Y 11M"), IF(MONTH(today() > MONTH([Column4]7), YEAR(today() - YEAR([Column4]7) + "Y " + (MONTH(today() - MONTH([Column4]7) + IF(DAY(today() >= DAY([Column4]7), 0, -1)) + "M", YEAR(today() - YEAR([Column4]7) - 1 + "Y " + (MONTH(today() - MONTH([Column4]7) + IF(DAY(today() > DAY([Column4]7), 1, 12)) + "M"))

It says incorrect argument set

• ✭✭✭✭✭✭
Options

Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭
Options

Smartsheet needs a YEARFRAC() formula like in Excel

• Options

For simple calculation of the age in years reached by the individual as of TODAY(), I wrote this formula that seems to be working in my sheet:

=IF(MONTH(TODAY()) > MONTH([DOB:]@row), YEAR(TODAY()) - YEAR([DOB:]@row), IF(MONTH(TODAY()) < MONTH([DOB:]@row), YEAR(TODAY()) - YEAR([DOB:]@row) - 1, IF(DAY(TODAY()) >= DAY([DOB:]@row), YEAR(TODAY()) - YEAR([DOB:]@row), IF(DAY(TODAY()) < DAY([DOB:]@row), YEAR(TODAY()) - YEAR([DOB:]@row) - 1))))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!