count months
 

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

count months
 

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

"Satyendra_Haldaur" wrote:

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

count months
 

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Satyendra_Haldaur" wrote in
message ...
can anyone pls suggest that what is best way to calculate months between
two
dates.

count months
 

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?

--
Rick (MVP - Excel)


"Satyendra_Haldaur" wrote in
message ...
can anyone pls suggest that what is best way to calculate months between
two
dates.

count months
 

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" wrote:

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?

--
Rick (MVP - Excel)


"Satyendra_Haldaur" wrote in
message ...
can anyone pls suggest that what is best way to calculate months between
two
dates.

count months
 

Or, for inclusive months, maybe this...

=Month(B1)-Month(A1)

--
Rick (MVP - Excel)


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

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?

--
Rick (MVP - Excel)


"Satyendra_Haldaur" wrote in
message ...
can anyone pls suggest that what is best way to calculate months
between
two
dates.

count months
 

Only if they are both in the same year.
--
David Biddulph

Rick Rothstein wrote:
Or, for inclusive months, maybe this...

=Month(B1)-Month(A1)


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

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?

--
Rick (MVP - Excel)


"Satyendra_Haldaur"
wrote in message
...
can anyone pls suggest that what is best way to calculate months
between
two
dates.

count months
 

99 programmers = 198 solutions!! :-)

"Rick Rothstein" wrote:

Or, for inclusive months, maybe this...

=Month(B1)-Month(A1)

--
Rick (MVP - Excel)


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

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?

--
Rick (MVP - Excel)


"Satyendra_Haldaur" wrote in
message ...
can anyone pls suggest that what is best way to calculate months
between
two
dates.

count months
 

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

Regards,
Fred.

"Rick Rothstein" wrote in message
...
Or, for inclusive months, maybe this...

=Month(B1)-Month(A1)

--
Rick (MVP - Excel)


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

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?

--
Rick (MVP - Excel)


"Satyendra_Haldaur" wrote
in
message ...
can anyone pls suggest that what is best way to calculate months
between
two
dates.