How calculate exact # of months between two dates?

Mark Livingstone

unread,

Jul 21, 2009, 2:31:12 PM7/21/09

to

Hi,

Right now I am using the DATEDIF(CELL1, CELL2, "m") function. However,
it provides only single digit response. So, if the two dates are 20
JUNE and 20 SEPTEMBER, it will be 2 months. However, if the two dates
are 20 JUNE and 20 SEPTEMBER, it give me 1 month instead of 1 month
and X days (or, 1.x << this is exactly output that I am trying to
achieve)

Any idea how to do that? Excel 2003.

Thanks!

Eduardo

unread,

Jul 21, 2009, 2:40:01 PM7/21/09

to

Hi,

=DATEDIF(D5,E5,"y")&" years "&DATEDIF(D5,E5,"ym")&" months
"&DATEDIF(D5,E5,"md")&" days"

Change range to fit your needs

if this helps please click yes, thanks

Mike H

unread,

Jul 21, 2009, 2:45:01 PM7/21/09

to

Hi,

Just a personal opinion but month is just about the daftest interval for
measuring time there is, is a month 28,29,30 or 31 days Hmm.

Anyway, I think this is what you want

=DATEDIF(A1,B1,"m")&"."&DATEDIF(A1,B1,"md")

For help look here

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

Mike

Mike H

unread,

Jul 21, 2009, 2:52:01 PM7/21/09

to

Eduardo,

test your formula with these 2 dates

31/01/1943
01/03/2008

There are countless combinations that throw up this anomaly

Mike

Mark Livingstone

unread,

Jul 21, 2009, 3:00:29 PM7/21/09

to

Thank you Eduardo and Mike H.

Mike H's formula worked. This is exactly what I was looking for :)

Mark Livingstone

unread,

Jul 21, 2009, 3:03:29 PM7/21/09

to

On Jul 21, 2:45 pm, Mike H <Mi...@discussions.microsoft.com> wrote:
> Hi,
>
> Just a personal opinion but month is just about the daftest interval for
> measuring time there is, is a month 28,29,30 or 31 days Hmm.
>
> Anyway, I think this is what you want
>
> =DATEDIF(A1,B1,"m")&"."&DATEDIF(A1,B1,"md")

The difference between 7/22/2009 and 8/21/2009 appears to be 0.30.
Odd.

Eduardo

unread,

Jul 21, 2009, 3:02:09 PM7/21/09

to

Hi Mike,
I check the formula and give me 65 years, 1month and 1 day, if I apply the
formula given by Cpearson in the link you sent it give me the same result,

I am intereste to know where the difference is, sorry if it's obvious but I
don't see it

Mike H

unread,

Jul 21, 2009, 3:15:01 PM7/21/09

to

Eduardo,

Your formula returns

65 years 1 months -1 days

for those 2 dates. The second date is 1 March 2008. It is a well documented
error in datedif and I have a pet theory (probably incorrect) that this is
the reason it isn't documented.

Mike

Mike H

unread,

Jul 21, 2009, 3:20:06 PM7/21/09

to

Eduardo,

It's probably clearer if you just use the "md" part of your formula

=DATEDIF(D5,E5,"md")&" days"

and note it returns (minus) -1 days

Mike

Ashish Mathur

unread,

Jul 21, 2009, 8:23:18 PM7/21/09

to

Hi,

Is there a date range over which this anomaly occurs I.e. what does an end
user need to know (do's and don’t's) before using the DATEDIF() function.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Mike H" <Mi...@discussions.microsoft.com> wrote in message
news:0AA01123-D69E-4279...@microsoft.com...