Formula to calculate exact months and days between two dates

[K]

In cell A1 and B1 I have dates (see below)
30/05/2006 12/08/2009
In cell A2 I have formula (see below) which produce the result 39
months
(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)

I am looking for formula which should tell me exact months and days
between two dates as difference in above dates are 38 months and 12
days but I don’t know how to achive this by formula. Please can any
friend can help

 

[Bernie Deitrick]

K,

=DATEDIF(A1,B1,"m") & " months, " & DATEDIF(A1,B1,"md") & " days"

HTH,
Bernie
MS Excel MVP


In cell A1 and B1 I have dates (see below)
30/05/2006 12/08/2009
In cell A2 I have formula (see below) which produce the result 39
months
(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)

I am looking for formula which should tell me exact months and days
between two dates as difference in above dates are 38 months and 12
days but I don’t know how to achive this by formula. Please can any
friend can help

 

[Rick Rothstein]

You might want to reconsider recommending the use of the DATEDIF function.
It is an undocumented (and, thus, probably and unsupported) Excel function
which appears to be broken in XL2007 at Service Pack 2. Someone recently
posted this message as part of a newsgroup question...

**********************************************************************
=DATEDIF(DATE(2009,6,27),DATE(2012,1,5),"md")

In 2007, this gives me 122. This happens all the way up to the point
where the second date is 1/26/2012 and then it hits zero at 1/27/2012.
In 2002, however, it gives me the correct answer of 9.
**********************************************************************

An informal survey of fellow MVPs shows the above formula works correctly in
the initial release of XL2007 and its SP1, but does not work correctly in
SP2; hence, it appears to be broken at that level. The problem is that the
extent of the breakage is unknown (and probably indeterminable). In
addition, I would say, being an undocumented (and, thus, probably and
unsupported) function, the odds of Microsoft spending the time to search
down and fix whatever broke is slim. This would seem to mean that DATEDIF
cannot be counted on to work correctly from XL2007 SP2 onward.

Note: I just tried your posted formula in my copy of XL2007-SP2 with June
27, 2009 in A1 and January 5, 2012 in B1 and the result was...

30 months 122 days

which is, of course, not correct.

 

[Ron Rosenfeld]

In cell A1 and B1 I have dates (see below)
30/05/2006 12/08/2009
In cell A2 I have formula (see below) which produce the result 39
months
(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)

I am looking for formula which should tell me exact months and days
between two dates as difference in above dates are 38 months and 12
days but I don’t know how to achive this by formula. Please can any
friend can help

That's not easy to do; in particular because the "exact" number of days in a
month can vary from 28-31.

If you are looking for "exact" intervals, you would be better off using days
and weeks (which do not vary in length).

If not, then you need to very carefully define what you mean by a "month" and
how you are going to count, with regard to dates in the range 28-31.

In your example, however, I would have thought the correct answer to be 38
months and 13 days; not 12.

I would have figured that the 38 "months" would be 30 May 2006 through 30 Jul
2009. That leaves 31 July 2009 + the 12 days in August which would be 13 days.
--ron