Count total Years, Months and Days between two dates in Excel

In this post we will see how we can calculate total number of years, months and days between two dates.

For example: If the start date is 2 October 2021 and end date is 3 November 2022 then we should get 1year 1month 1day.

DATEDIF - Formula

DATEDIF(start_date, end_date,“Y”) => Provides total number of years between starts and end date. In above example, the total number of yeats between the dates is one year.

DATEDIF(start_date, end_date,“YM”) => Provided the total number of months between dates and ignores the days and year. Total number of months is one year since it is ignoring year and days in the above example.

DATEDIF(start_date, end_date,“MD”) => Provided the total number of months between dates and ignore the days and years. In the above example, the total days by ignoring month and year is just one.

We can combine the above three formulas to get “1year 1month 1day” for the start date and end date between 2 October 2021 and 3 November 2022.

=DATEDIF(A1,B1,"Y") & "year " & DATEDIF(A1,B1,"YM") & "month " & DATEDIF(A1,B1,"MD") & "day"

Using DATEDIF with IF function

We will take an example of the table below where we will use the purchase date as the start date and find “Held for” time frame which is basically the total number of time the Item is held for. If the item is not sold then the time span will be between the date of purchased and date now otherwise the “Held for” time span will be between puchased and sold date.

Below is the formula:

=IF(B2="",
(
IF(DATEDIF([@[Purchased on]],TODAY(),"Y") >0, DATEDIF([@[Purchased on]],TODAY(),"Y") & "y ", "") &
IF(DATEDIF([@[Purchased on]],TODAY(),"YM") >0, DATEDIF([@[Purchased on]],TODAY(),"YM") & "m ", "") &
IF(DATEDIF([@[Purchased on]],TODAY(),"MD") >0, DATEDIF([@[Purchased on]],TODAY(),"MD") & "d", "")
),
(
IF(DATEDIF([@[Purchased on]],[@[Sold On]],"Y") >0, DATEDIF([@[Purchased on]],[@[Sold On]],"Y") & "y ", "") &
IF(DATEDIF([@[Purchased on]],[@[Sold On]],"YM") >0, DATEDIF([@[Purchased on]],[@[Sold On]],"YM") & "m ", "") &
IF(DATEDIF([@[Purchased on]],[@[Sold On]],"MD") >0, DATEDIF([@[Purchased on]],[@[Sold On]],"MD") & "d", "")
)
)

Here, we are performing the following:

• Checking if the “Sold On” date is empty or mentioned
• If “Sold On” is not mentioned that the item is still held with person and the end date is taken as today.
• If “Sold On” date is mentioned then it is the end date instead.
• We are also checking if a year or month or day is 0, in that case we are not mentioning it as 0, rather we are skipping it. So, in place of “0years, 0month, 1day”, we will get just “1day”

Hope this formula is useful to you.