S
Satyendra_Haldaur
can anyone pls suggest that what is best way to calculate months between two
dates.
dates.
M
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
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
R
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
=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
R
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?
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?
J
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.
=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.
R
Rick Rothstein
Or, for inclusive months, maybe this...
=Month(B1)-Month(A1)
=Month(B1)-Month(A1)
J
JLatham
99 programmers = 198 solutions!! 
Rick Rothstein said:
F
Fred Smith
Or, if the dates can be in different years,
=year(b1)*12+month(b1)-year(a1)*12-month(a1)
Regards,
Fred.
=year(b1)*12+month(b1)-year(a1)*12-month(a1)
Regards,
Fred.