Calculating Exact Days, Months & Years between 2 dates

Heyo,

I have been trying to come up with a formula that calculates the exact days, months and years between 2 dates so it reads out as x years, x months, x days. I got it working for the most part, but the issue that I am running into now is when the month is in the future. Here is the formula

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

So this works perfectly for everyone who has a hire date with a month of jan or feb, but anyone with a future month it is calculating extra time

Screenshot 2023-02-15 134929.png

Find more posts tagged with

Accepted answers

All comments

Jeff Reisman

@Jen Vaihinger I think the flaw in your logic is that you're considering years before days and months, when the formula doesn't yet know if those are all whole years, because it hasn't looked at months yet. But there are avenues available to fix this.

For instance, if you subtract the hire date from today, you get the number of days as a result. If you divide the result by 365, you get the number of whole years and a decimal. So today(2/15/23) minus 8/12/14 = 3109. Then, 3109 / 365 = 8.51781. You can use the INT function to just take the integer portion of the result, and there's your number of whole years. With your current method of just subtracting hire year from current year, you'd get 9, which is wrong.

So here is your new "years" portion of your formula:

=IF(ISBLANK([EBS Hire Date]@row), [ESE Employment]@row, (INT((TODAY() - [EBS Hire Date]@row) / 365)

NickAC

I'm trying to do something similar with a column called Employee Start Date. Here's what I have so far... where did I break it?

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

Jeff Reisman

@NickAC

It would be helpful if you could a screenshot of your color-coded formula within Smartsheet, along with whatever error message or result you are getting.

Also, I am assuming all your date columns are actually formatted as Date type, yes?

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.