Is there a formula to calculate years, months, and days between 2 different dates?

I found a formula that calculates the years and months between 2 different dates, but because it's not also calculating the days, it is rounding up the months which isn't going to work in the case that I need it.

Here is a picture of what I'm looking for (how many years, months, and days) Purchase Date to Expiration Date.

The formula that I'm currently using is:

=ROUND(ROUND((NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365) * 12) / 12) + "yrs" + MOD(ROUND((NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365) * 12), 12) + "mo"

Please let me know if I'm missing something to be able to also add in the day count!

Thank you

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul H

Not sure if you are going to get it perfect down to the day. I tried adding three helper columns and came up close.

Years =NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24

Months =VALUE(RIGHT(Years@row, LEN(Years@row) - (FIND(".", Years@row)) + 1)) * 12

Days =VALUE(RIGHT(Months@row, LEN(Months@row) - (FIND(".", Months@row)) + 1)) * 30.437

Date Run Count =LEFT(Years@row, FIND(".", Years@row) - 1) + "yrs " + LEFT(Months@row, FIND(".", Months@row) - 1) + "mo " + LEFT(Days@row, FIND(".", Days@row) - 1) + "days "

Jillian Lipstone

@Paul H thank you for looking into this! Do you know if there might be a way to do it without the helper columns? If not, no worries--just wanted to check!

Paul H

=LEFT(NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24, FIND(".", NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24) - 1) + "yrs " + LEFT(VALUE(RIGHT(NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24, LEN(NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24) - (FIND(".", NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24)) + 1)) * 12, FIND(".", VALUE(RIGHT(NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24, LEN(NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24) - (FIND(".", NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24)) + 1)) * 12) - 1) + "mo " + LEFT(VALUE(RIGHT(VALUE(RIGHT(NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24, LEN(NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24) - (FIND(".", NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24)) + 1)) * 12, LEN(VALUE(RIGHT(NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24, LEN(NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24) - (FIND(".", NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24)) + 1)) * 12) - (FIND(".", VALUE(RIGHT(NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24, LEN(NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24) - (FIND(".", NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24)) + 1)) * 12)) + 1)) * 30.437, FIND(".", VALUE(RIGHT(VALUE(RIGHT(NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24, LEN(NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24) - (FIND(".", NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24)) + 1)) * 12, LEN(VALUE(RIGHT(NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24, LEN(NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24) - (FIND(".", NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24)) + 1)) * 12) - (FIND(".", VALUE(RIGHT(NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24, LEN(NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24) - (FIND(".", NETDAYS([Purchase Date]@row, [Expiration Date]@row) / 365.24)) + 1)) * 12)) + 1)) * 30.437) - 1) + "days "

Jillian Lipstone

@Paul H thank you so much!!!

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.