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

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

User: "Screenshot 2023-09-05 175245.png"

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

Any suggestions?

Thank you!

Kacey

Find more posts tagged with

Accepted answers

Nick Korna

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! 😊

All comments

Nick Korna

Hi @Kacey W

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

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

Kacey W

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

elambert

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

smdinhphan

I wish Smartsheet had this functionality like Excel's DATEDIF function. I had to resort to creating formulas from scratch to replicate it. There are separate formulas to calculate the years, months-in-excess-of-years, and days-in-excess-of-months. Create 3 columns 'Years', 'Months', 'Days'. The following example uses today's date for calculation (7/30/2024). If you have static End Dates, create an End Date column and replace TODAY() with its reference cell.

Smartsheet Example

Excel Example using DATEDIF()

Years column formula:

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

Months column formula:

=IF(DAY(TODAY()) < DAY([Start Date]@row), (IF(MONTH(TODAY()) <= MONTH([Start Date]@row), MONTH(TODAY()) - MONTH([Start Date]@row) + 11, MONTH(TODAY()) - MONTH([Start Date]@row) - 1)), (IF(MONTH(TODAY()) < MONTH([Start Date]@row), MONTH(TODAY()) - MONTH([Start Date]@row) + 12, MONTH(TODAY()) - MONTH([Start Date]@row))))

Days column formula:

=IF(DAY(TODAY()) >= DAY([Start Date]@row), DAY(TODAY()) - DAY([Start Date]@row), IF(OR(MONTH(TODAY()) = 1, MONTH(TODAY()) = 2, MONTH(TODAY()) = 4, MONTH(TODAY()) = 6, MONTH(TODAY()) = 8, MONTH(TODAY()) = 9, MONTH(TODAY()) = 11), 31 - DAY([Start Date]@row) + DAY(TODAY()), IF(OR(MONTH(TODAY()) = 5, MONTH(TODAY()) = 7, MONTH(TODAY()) = 10, MONTH(TODAY()) = 12), 30 - DAY([Start Date]@row) + DAY(TODAY()), IF(AND(MONTH(TODAY()) = 3, MOD(YEAR(TODAY()), 4) = 0), 29 - DAY([Start Date]@row) + DAY(TODAY()), IF(AND(MONTH(TODAY()) = 3, MOD(YEAR(TODAY()), 4) <> 0), 28 - DAY([Start Date]@row) + DAY(TODAY()), "ERROR.")))))

Years, Months, Days Concat formula:

=Years@row + " Year" + IF(Years@row = 1, ", ", "s, ") + Months@row + " Month" + IF(Months@row = 1, ", ", "s, ") + Days@row + " Day" + IF(Days@row = 1, "", "s")

I believe this will do what you are looking for and also accounts for leap years. I tested a wide range of dates, so this should work exactly as Excel's DATEDIF().

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.