Calculate Age from birthdate

Nicole Graham

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

L_123

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"

J. Craig Williams

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

L_123

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

J. Craig Williams

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

J. Craig Williams

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

L_123

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

J. Craig Williams

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

Craig

 

J. Craig Williams

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

J. Craig Williams

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

Jackson Khoo

column named DOB has date of birth

DOB = 3 Jan 1988

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

Paul Newcome

@Jackson Khoo

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.

Jill Rovira

Hi J. Craig Williams

I tried your formula and it did not work. Please HELP!!!

=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

Andrée Starå

Hi @Jill Rovira

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

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Adrian Mandile CHESS

Smartsheet needs a YEARFRAC() formula like in Excel

Kevin Platt

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