Calculating Exact Days, Months & Years between 2 dates

Jen Vaihinger
Jen Vaihinger ✭✭
edited 02/15/23 in Formulas and Functions

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


Answers

  • Jeff Reisman
    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)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • NickAC
    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
    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?

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!