# calculate difference between two dates in year, month and day in excel

How to calculate the difference between two dates in years, months and days in excel. Let say first date is  01/15/1996 and second date is 09/26/2011. I want the answer in years, months and days.

* Please try a lower page number.

* Please try a lower page number.

Use the =Datedif() function.  See:

http://www.cpearson.com/excel/datedif.aspx

GSNU201507

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Hi,
Let's say that you have
in A1 01/15/1996
in A2 09/26/2011
in cell A3 write:
=DAYS360(A1,A2)

in cell A4: =INT(A3/360)....years 15
in cell A5: =INT(MOD(A3,360)/30).....months 8
and in A6: =A3-A4*360-A5*30.....days 11

-----------------------------
Office 365 on Windows 10

4 people were helped by this reply

·

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

In A1 I put

 12/2/2011

In A2 I put

 1/1/2012

A3 returns

 29

A4 returns

 0

A5 returns

 0

and

A6 returns

 29

But simple counting indicates 31 days.

GSNU201507

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

There are problems with using the Datedif function to compute a time difference in years, months and days.

For example, using the formula Chip has on the website, with

A1:  31 Jan 2009
B1:   1 Mar 2011

Result --> 2 years, 1 months, -2 days

Ron

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

That seems like a simple question, but because years and months are not precise intervals (they can both vary in length), the answer is not always straightforward and depends on how exact you need to be, and how you want to handle partial months.
Ron

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

In A1 I put

 12/2/2011

In A2 I put

 1/1/2012

A3 returns

 29

A4 returns

 0

A5 returns

 0

and

A6 returns

 29

But simple counting indicates 31 days.

That counting is, indeed 31 days including both the start and end dates, but I would argue that since the OP asked for the "difference", I would count it as 30 days.

By the way, using Chip's Datedif formula with those two dates returns a result of 143 days due to a flaw in the "md" option that was introduced in Excel 2007 SP2
Ron

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

I use a User Defined Function for this calculation.  It works by counting the number of "end of month" dates between the starting and ending dates; 1/12 of that is the number of years; and the last "end of month" date is used to compute the remaining # of days.

We can quibble a bit as to how to count where the starting date is Feb 29.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=DateIntvl(A1,A2)

in some cell.

============================================
Option Explicit
Function DateIntvl(d1 As Date, d2 As Date) As String
'Note that if d1 = 29 Feb, the definition of a year
'may not be the same as the legal definition in a
'particular locale
'Some US states, for some purposes, declare a
'leapling's birthday on 1 Mar in common years; England
'and Taiwan declare it on Feb 28
Dim Temp As Date
Dim i As Double
Dim yr As Long, mnth As Long, dy As Long
Dim sOutput() As String

Do Until Temp > d2
i = i + 1
Loop

i = i - 1

yr = Int(i / 12)
mnth = i Mod 12
dy = d2 - Temp

ReDim sOutput(0 To -(yr > 0) - (mnth > 0) - (dy > 0) - 1)
i = 0
If yr > 0 Then
sOutput(i) = yr & IIf(yr = 1, " Year", " Years")
i = i + 1
End If
If mnth > 0 Then
sOutput(i) = mnth & IIf(mnth = 1, " Month", " Months")
i = i + 1
End If
If dy > 0 Then sOutput(i) = dy & IIf(dy = 1, " Day", " Days")

DateIntvl = Join(sOutput, ", ")

End Function
===================

Ron

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

You are correct about counting both the start and end dates.  Another approach is Yogesh Gupta's described in:

http://www.yogeshguptaonline.com/

The formula is obese, yet robust.

GSNU201507

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

It still has what I would call inconsistencies in dealing with date spans where the ending date of the start month is later than the ending date of the month preceding the end month.  For example (after replacing TODAY() in Gupta's formula with a cell reference):

Ron

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

* Please try a lower page number.