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

Jillian Lipstone

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

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