count months

[Satyendra_Haldaur]

can anyone pls suggest that what is best way to calculate months between two
dates.

 

[Mike H]

Hi,

It depends what you mean by 'between' but as a start try this

=datedif(A1,B1,"m")

For help on the undocumented datedif look here

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

Mike

 

[Ragdyer]

With the earlier date in A1:

=DATEDIF(A1,B1,"m")

Check out this web page of Chip Pearson for an explanation of this
undocumented (except for XL2k) function.

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

 

[Rick Rothstein]

DATEDIF(StartDate,EndDate,"m") **may** be what you are looking for; but then
again, it may not. You need to understand how it "counts" months in order to
decide. Consider a start date of March 15, 2009 and an end date of June 15,
2009... DATEDIF will report this as 3 months; **however**, change the start
date to March 16, 2009 and DATEDIF now reports this as 2 months. It appears
that DATEDIF counts full months starting its count from the starting date.
Is that what you wanted?

 

[JLatham]

If you wanted the result to always be 3 (inclusive of all dates) you can try:
=DATEDIF(DATE(YEAR(A1),MONTH(A1),1),DATE(YEAR(B1),MONTH(B1),1),"m")
That changes both dates to the first of both months used in the DATEDIF() so
the 15th/16th problem doesn't come into play.
I actually tried using the EOMONTH() function, but that was still
susceptible to the variance depending on the end date of the months involved.

 

[Rick Rothstein]

Or, for inclusive months, maybe this...

=Month(B1)-Month(A1)

 

[JLatham]

99 programmers = 198 solutions!!

Or, for inclusive months, maybe this...

=Month(B1)-Month(A1)

 

[Fred Smith]

Or, if the dates can be in different years,
=year(b1)*12+month(b1)-year(a1)*12-month(a1)

Regards,
Fred.