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

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“
    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! 😊

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer βœ“
    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! 😊

  • Kacey W
    Kacey W ✭✭
    Options

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

  • elambert
    elambert ✭✭✭
    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!