Exact age to the year, month and day as of today's date.

Jill Rovira

Hi I am in the process of switching to Smartsheet and I have a formula in excel the provides me with a child's age exactly to the year, month and day as of todays date. I have been trying to find a formula that works in Smartsheet to provide me with the same data. Every formula I have found has rounded the age to the year.

Can anyone please help me?

David Tutwiler

I think this will get you close:

=YEAR(TODAY()) - YEAR([Due Date]@row) + " years " + IF(MONTH(TODAY()) - MONTH([Due Date]@row) < 0, 12 + MONTH(TODAY()) - MONTH([Due Date]@row), MONTH(TODAY()) - MONTH([Due Date]@row)) + " months " + IF(DAY(TODAY()) - DAY([Due Date]@row) < 0, 30 + DAY(TODAY()) - DAY([Due Date]@row), DAY(TODAY()) - DAY([Due Date]@row)) + " days"

David Tutwiler

I think this will get you close:

=YEAR(TODAY()) - YEAR([Due Date]@row) + " years " + IF(MONTH(TODAY()) - MONTH([Due Date]@row) < 0, 12 + MONTH(TODAY()) - MONTH([Due Date]@row), MONTH(TODAY()) - MONTH([Due Date]@row)) + " months " + IF(DAY(TODAY()) - DAY([Due Date]@row) < 0, 30 + DAY(TODAY()) - DAY([Due Date]@row), DAY(TODAY()) - DAY([Due Date]@row)) + " days"

Jill Rovira

That worked!!! THANK YOU SO MUCH!

Jill Rovira

It worked for some of the dates, but others are slightly off. Any idea how to fix?

04/01/20 0 years 6 months 7 days

04/01/20 0 years 6 months 7 days

02/29/20 0 years 8 months 9 days

12/08/19 1 years 10 months 0 days

10/12/19 1 years 0 months 26 days

09/03/19 1 years 1 months 5 days

08/04/19 1 years 2 months 4 days

05/20/19 1 years 5 months 18 days

Date of Birth Age as of Current Date

05/17/20 0 years 5 months 21 days

02/27/20 0 years 8 months 11 days

02/02/20 0 years 8 months 6 days

01/31/20 0 years 9 months 7 days

11/08/19 1 years 11 months 0 days

09/19/19 1 years 1 months 19 days

08/22/19 1 years 2 months 16 days

This is the formula I hope I didn't miss type.

=YEAR(TODAY()) - YEAR([Column4]23) + " years " + IF(MONTH(TODAY()) - MONTH([Column4]23) < 0, 12 + MONTH(TODAY()) - MONTH([Column4]23), MONTH(TODAY()) - MONTH([Column4]23)) + " months " + IF(DAY(TODAY()) - DAY([Column4]23) < 0, 30 + DAY(TODAY()) - DAY([Column4]23), DAY(TODAY()) - DAY([Column4]23)) + " days"

David Tutwiler

I do know what's happening, the how to fix it is a little more difficult.

This formula assumes 30 days in every month (see the 30 + DAY(etc etc)).

Because of this, if your timespan goes over some 31 day months and Feb., then the calculation will be slightly off. The only way I know to fix that would be to add more IFs to check to see if February is one of the months or get a list of all of the 31 day months and check for those too. Depends on how accurate you have to have it.

Jill Rovira

Can you help me with this? I do need it as accurate as possible. If you look above some of them the year and day is correct, but the month is off or the month and day is correct but the year is off.

This is the formula I am using with excel and it is 100% accurate:

= DATEDIF(D9,TODAY(),"Y") & " Years, " & DATEDIF(D9,TODAY(),"YM") & " Months, " & DATEDIF(D9,TODAY(),"MD") & " Days"

months with 31 days:

January

March

May

July

August

October

December

February - 28

Thank you so much for taking your time in helping me :)

David Tutwiler

Unfortunately, I don't think Smartsheet has a similar formula. I'm not sure I can cobble anything together that would suit this. If you have an account rep I would reach out to them, or schedule a Pro Desk session. They may have a better answer.

Jill Rovira

Is there any way to at least have the exact year and month?