Counting months
 

I have been trying without success to use the MONTH function to calculate a
period of months between two dates. My aim is to take, say, cell A1 which
has 11/07/05 and deduct it from cell B1 which has 10/07/06 and for the result
to be 12 which it should be.

For the most part, MONTH(B1)-MONTH(A1) works fine. However, it doesn't work
when the total number of months exceed 12... I don't want to jam up my sheet
with formulae (the cell will be replicated 200 times), with IF statements and
concatenates...

Is there a simple and effective use for getting the month value in a period
of more than 12 months?

Have you tried using DATEDIF

=DATEDIF(A1,B1,"m")


--
Message posted via http://www.officekb.com

Additionally, I found this formula on the KB:

=IF(ISNUMBER(V2),(YEAR(V2)-YEAR(U2))*12+MONTH(V2)-MONTH(U2),0)

But this does not work where the beginning month (U2) is the first of the
month (i.e. 01/07/05) and the end month the end of a month (30/06/06) -
technically 12 months but shown as 11.

"Aaron Howe" wrote:

I have been trying without success to use the MONTH function to calculate a
period of months between two dates. My aim is to take, say, cell A1 which
has 11/07/05 and deduct it from cell B1 which has 10/07/06 and for the result
to be 12 which it should be.

For the most part, MONTH(B1)-MONTH(A1) works fine. However, it doesn't work
when the total number of months exceed 12... I don't want to jam up my sheet
with formulae (the cell will be replicated 200 times), with IF statements and
concatenates...

Is there a simple and effective use for getting the month value in a period
of more than 12 months?

That does achieve my aim, but leaves me with the 11-month issue where the
"start" date is the first of a month...

"VoG via OfficeKB.com" wrote:

Have you tried using DATEDIF

=DATEDIF(A1,B1,"m")


--
Message posted via http://www.officekb.com

That is a somewhat particular definition of a month (certainly not
technically 12 months). But if it really is what you require, just add 1 to
the end date in the DATEDIF function.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"Aaron Howe" wrote in message
...
Additionally, I found this formula on the KB:

=IF(ISNUMBER(V2),(YEAR(V2)-YEAR(U2))*12+MONTH(V2)-MONTH(U2),0)

But this does not work where the beginning month (U2) is the first of the
month (i.e. 01/07/05) and the end month the end of a month (30/06/06) -
technically 12 months but shown as 11.

"Aaron Howe" wrote:

I have been trying without success to use the MONTH function to calculate
a
period of months between two dates. My aim is to take, say, cell A1
which
has 11/07/05 and deduct it from cell B1 which has 10/07/06 and for the
result
to be 12 which it should be.

For the most part, MONTH(B1)-MONTH(A1) works fine. However, it doesn't
work
when the total number of months exceed 12... I don't want to jam up my
sheet
with formulae (the cell will be replicated 200 times), with IF statements
and
concatenates...

Is there a simple and effective use for getting the month value in a
period
of more than 12 months?

I don't think this group is the right place to start bickering about what
constitutes 12 months (to midnight, yes it does thanks). Adding 1 would
therefore throw out the rest of the calculations...

Anyone else managed a workaround for this?

"Niek Otten" wrote:


That is a somewhat particular definition of a month (certainly not
technically 12 months). But if it really is what you require, just add 1 to
the end date in the DATEDIF function.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

"Aaron Howe" wrote in message
...
Additionally, I found this formula on the KB:

=IF(ISNUMBER(V2),(YEAR(V2)-YEAR(U2))*12+MONTH(V2)-MONTH(U2),0)

But this does not work where the beginning month (U2) is the first of the
month (i.e. 01/07/05) and the end month the end of a month (30/06/06) -
technically 12 months but shown as 11.

"Aaron Howe" wrote:

I have been trying without success to use the MONTH function to calculate
a
period of months between two dates. My aim is to take, say, cell A1
which
has 11/07/05 and deduct it from cell B1 which has 10/07/06 and for the
result
to be 12 which it should be.

For the most part, MONTH(B1)-MONTH(A1) works fine. However, it doesn't
work
when the total number of months exceed 12... I don't want to jam up my
sheet
with formulae (the cell will be replicated 200 times), with IF statements
and
concatenates...

Is there a simple and effective use for getting the month value in a
period
of more than 12 months?