Formula for calculating years accurately

StephenLake

We need a formula for calculating years accurately when we compare a date with the date today – some examples would be:

Age in years: Today() - date of birth, currently we use: =(TODAY() – DOB1) / 365.25 but it doesn’t always give the correct answer. The 365.25 is days in a year allowing for leap years, or
Length of service in years: Today() – date joined, currently we use: =(TODAY() - [Started Work]1) / 365.25

Can anyone suggest a formula that works in all cases including where there are leap years in the intervening period?

Find more posts tagged with

Human Resources

Formulas

Services

Comments

SmSulli

Here's a formula I created to calculate years for you. Let me know if this works for you.

Age:

=IF(AND(MONTH(DOB1) >= MONTH(TODAY()), DAY(DOB1) > DAY(TODAY())), YEAR(TODAY()) - 1 - YEAR(DOB1), YEAR(TODAY()) - YEAR(DOB1))

Years of Service:

=IF(AND(MONTH([Start Date]1) >= MONTH(TODAY()), DAY([Start Date]1) > DAY(TODAY())), YEAR(TODAY()) - 1 - YEAR([Start Date]1), YEAR(TODAY()) - YEAR([Start Date]1))

Enjoy!

Community Help.JPG

StephenLake

Thank you so much for your help - it works well.

Are you available to consult to us?

The next two formulas not working I need help with are:

Next Birthday:

=DOB3 + ([Age next Birthday]3 * 365.25) + 1

Next [Service] Anniversary:

=[Started Work]3 + ([Service next Anniversary]3 * 365.25)

There is probably a smarter way to do these.

Attached a screenshot. Please note dates are in DD/MM/YYYY format

SmartSheet HR screen capture 20170504.JPG

SmSulli

Thank you so much for the compliment. I'm a bit of an Excel nut too. But, I did come across an error myself. So I've made new formulas for Age and Years of Service. Also included formulas for Next Birthday, and Next Anniversary. I made the Next birthday and next anniversary formatted to DD/MM/YYYY.

Age:

=IF(MONTH(DOB1) > MONTH(TODAY()), YEAR(TODAY()) - 1 - YEAR(DOB1), IF(AND(MONTH(DOB1) >= MONTH(TODAY()), DAY(DOB1) > DAY(TODAY())), YEAR(TODAY()) - 1 - YEAR(DOB1), YEAR(TODAY()) - YEAR(DOB1)))

Years of Service:

=IF(MONTH([Start Date]1) > MONTH(TODAY()), YEAR(TODAY()) - 1 - YEAR([Start Date]1), IF(AND(MONTH([Start Date]1) >= MONTH(TODAY()), DAY([Start Date]1) > DAY(TODAY())), YEAR(TODAY()) - 1 - YEAR([Start Date]1), YEAR(TODAY()) - YEAR([Start Date]1)))

Next Birthday:

=IF(MONTH(DOB1) > MONTH(TODAY()), DAY(DOB1) + "/" + MONTH(DOB1) + "/" + YEAR(TODAY()), IF(AND(MONTH(DOB1) >= MONTH(TODAY()), DAY(DOB1) > DAY(TODAY())), DAY(DOB1) + "/" + MONTH(DOB1) + "/" + YEAR(TODAY()), DAY(DOB1) + "/" + MONTH(DOB1) + "/" + (YEAR(TODAY()) + 1)))

Next Anniversary:

=IF(MONTH([Start Date]1) > MONTH(TODAY()), DAY([Start Date]1) + "/" + MONTH([Start Date]1) + "/" + YEAR(TODAY()), IF(AND(MONTH([Start Date]1) >= MONTH(TODAY()), DAY([Start Date]1) > DAY(TODAY())), DAY([Start Date]1) + "/" + MONTH([Start Date]1) + "/" + YEAR(TODAY()), DAY([Start Date]1) + "/" + MONTH([Start Date]1) + "/" + (YEAR(TODAY()) + 1)))

Enjoy!

J. Craig Williams

My take on the formulas, partially because the "Next" formulas assume DD/MM/YY and I'm (for better or worse) using MM/DD/YY.

Age:

=IF(DATE(YEAR(TODAY()), MONTH(DOB1), DAY(DOB1)) < TODAY(), YEAR(TODAY()) - YEAR(DOB1), YEAR(TODAY()) - YEAR(DOB1) - 1)

Next Birthday:

=IF(DATE(YEAR(TODAY()), MONTH(DOB1), DAY(DOB1)) < TODAY(), DATE(YEAR(TODAY()) + 1, MONTH(DOB1), DAY(DOB1)), DATE(YEAR(TODAY()), MONTH(DOB1), DAY(DOB1)))

Years of Service:

=IF(DATE(YEAR(TODAY()), MONTH([Start Date]1), DAY([Start Date]1)) < TODAY(), YEAR(TODAY()) - YEAR([Start Date]1), YEAR(TODAY()) - YEAR([Start Date]1) - 1)

Next Anniversary:

=IF(DATE(YEAR(TODAY()), MONTH([Start Date]1), DAY([Start Date]1)) < TODAY(), DATE(YEAR(TODAY()) + 1, MONTH([Start Date]1), DAY([Start Date]1)), DATE(YEAR(TODAY()), MONTH([Start Date]1), DAY([Start Date]1)))

The two "Next" columns are assumed to be Date type columns.

Also, if you want to get rid of those pesky #INVALID DATA TYPE messages:

=IFERROR(formula here, "Need a date")

like this

=IFERROR(IF(DATE(YEAR(TODAY()), MONTH(DOB1), DAY(DOB1)) < TODAY(), DATE(YEAR(TODAY()) + 1, MONTH(DOB1), DAY(DOB1)), DATE(YEAR(TODAY()), MONTH(DOB1), DAY(DOB1))), "Need a date")

Craig

Meritza Webb

I have tried all of these suggested formulas in an attempt to auto calculate age. None of the formulas are working for me. The results return as #DATE EXPECTED. Any new suggestions?

Meritza Webb

Disregard. I figured out the issue. I had the Age field formatted as a date instead of text. It works now! Thanks for your help.

Meritza Webb

I used this formula, but some of my ages are off by 1 year. Do you know why this may be happening?

=IF(AND(MONTH(DOB1) >= MONTH(TODAY()), DAY(DOB1) > DAY(TODAY())), YEAR(TODAY()) - 1 - YEAR(DOB1), YEAR(TODAY()) - YEAR(DOB1))