Formula to calculate exact months and days between two dates
 

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

Formula to calculate exact months and days between two dates
 

K,

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

HTH,
Bernie
MS Excel MVP


"K" wrote in message
...
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

Formula to calculate exact months and days between two dates
 

On Wed, 12 Aug 2009 08:24:47 -0700 (PDT), K wrote:

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