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 enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

Use the =Datedif() function.  See:

 

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

GSNU201507

Did this solve your problem?

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?

Thanks for your feedback.

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

·

Did this solve your problem?

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?

Thanks for your feedback.

Did this solve your problem?

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?

Thanks for your feedback.

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

Did this solve your problem?

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?

Thanks for your feedback.

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

Did this solve your problem?

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?

Thanks for your feedback.

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

Did this solve your problem?

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?

Thanks for your feedback.

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

Did this solve your problem?

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?

Thanks for your feedback.

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
    Temp = DateAdd("m", i, d1)
Loop

i = i - 1
Temp = DateAdd("m", i, d1)

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

Did this solve your problem?

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?

Thanks for your feedback.

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

Did this solve your problem?

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?

Thanks for your feedback.

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

Did this solve your problem?

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?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

 
 

Question Info


Last updated June 26, 2020 Views 10,191 Applies to: