# Calculating the difference in years, months, and days between two date ranges

Options
✭✭

Hello,

I am trying to calculate the difference between two date ranges and have the result display in years, months, and days. The formula is displayed below and also pasted here:

=IF(ISBLANK(DOB@row), [Event Date]@row, (INT(([Event Date]@row) - DOB@row) / 365) + " years " + IF(MONTH([Event Date]@row) - MONTH(DOB@row) < 0, 12 + MONTH([Event Date]@row) - MONTH(DOB@row), MONTH([Event Date]@row) - MONTH(DOB@row)) + " months " + IF(DAY([Event Date]@row) - DAY(DOB@row) < 0, 30 + DAY([Event Date]@row) - DAY(DOB@row), DAY([Event Date]@row) - DAY(DOB@row)) + " days")

My result is as show below; it not displaying time frame as I had hoped.

Any suggestions?

Thank you!

Kacey

• ✭✭✭✭✭✭
Options

Hi @Kacey W

Is this along the lines of what you're after?

=(IF(YEAR([Event Date]@row) - YEAR(DOB@row) > 1, YEAR([Event Date]@row) - YEAR(DOB@row) + " years ", IF(YEAR([Event Date]@row) - YEAR(DOB@row) = 1, YEAR([Event Date]@row) - YEAR(DOB@row) + " year ", "")) + IF(MONTH([Event Date]@row) - MONTH(DOB@row) < 0, 12 + MONTH([Event Date]@row) - MONTH(DOB@row), MONTH([Event Date]@row) - MONTH(DOB@row)) + " months " + IF(DAY([Event Date]@row) - DAY(DOB@row) < 0, 30 + DAY([Event Date]@row) - DAY(DOB@row) + " days", DAY([Event Date]@row) - DAY(DOB@row) + " days"))

Hope this helps, but if I've misunderstood something left me know! 😊

• ✭✭✭✭✭✭
Options

Hi @Kacey W

Is this along the lines of what you're after?

=(IF(YEAR([Event Date]@row) - YEAR(DOB@row) > 1, YEAR([Event Date]@row) - YEAR(DOB@row) + " years ", IF(YEAR([Event Date]@row) - YEAR(DOB@row) = 1, YEAR([Event Date]@row) - YEAR(DOB@row) + " year ", "")) + IF(MONTH([Event Date]@row) - MONTH(DOB@row) < 0, 12 + MONTH([Event Date]@row) - MONTH(DOB@row), MONTH([Event Date]@row) - MONTH(DOB@row)) + " months " + IF(DAY([Event Date]@row) - DAY(DOB@row) < 0, 30 + DAY([Event Date]@row) - DAY(DOB@row) + " days", DAY([Event Date]@row) - DAY(DOB@row) + " days"))

Hope this helps, but if I've misunderstood something left me know! 😊

• ✭✭
Options

That is exactly what I was trying to achieve - thank you so much!!!

• ✭✭✭
Options

I've looked at a few formulas to accomplish this task. Your answer is the closest. but I'm having an issue. It's increasing the year and month by 1 on some lines. In excel the 3rd line shows 3y 9m 25d. I'm using

=(IF(YEAR([End Date]@row) - YEAR([Hire Date]@row) > 1, YEAR([End Date]@row) - YEAR([Hire Date]@row) + " y", IF(YEAR([End Date]@row) - YEAR([Hire Date]@row) = 1, YEAR([End Date]@row) - YEAR([Hire Date]@row) + " y", "")) + IF(MONTH([End Date]@row) - MONTH([Hire Date]@row) < 0, 12 + MONTH([End Date]@row) - MONTH([Hire Date]@row), MONTH([End Date]@row) - MONTH([Hire Date]@row)) + " m" + IF(DAY([End Date]@row) - DAY([Hire Date]@row) < 0, 30 + DAY([End Date]@row) - DAY([Hire Date]@row) + " d", DAY([End Date]@row) - DAY([Hire Date]@row) + "d"))

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!