I need to calculate the difference in months between 01 October 2007 and 31
December 2007. When i am using the following formula
=MONTH(B3)-MONTH(B2)
i am getting a result of 2 although it should be 3....any ideas...help please

I need to calculate the difference in months between 01 October 2007 and 31
December 2007. When i am using the following formula
=MONTH(B3)-MONTH(B2)
i am getting a result of 2 although it should be 3....any ideas...help
please

Well, you could always add one to your formula, but I'm thinking you have a
deeper question hiding in here. Is your start date always the first of the
month? Is your end date always the end of the month? If yes, the "plus one"
fix should be enough. If not, then you need to tell us what answer you would
expect for these...

15 October 2007 and 15 December 2007
30 October 2007 and 01 December 2007
31 January 2008 and 28 February 2008
31 January 2008 and 01 March 2008

I'm sure there are other "defining parameters" that we probably need to ask
you about, but the above would get us started.

Rick

Thanks a lot Rick i had already figured out that i can use the +1 fix....but
doesnt it sound weird that Excel cannot calculate.....and yes you are right
my start date is always the beginning of the month and my finish date is
always the end of the month....for your answer to the 4 sample date ranges
you sent i would expect the following answers

15 October 2007 and 15 December 2007 2 months
30 October 2007 and 01 December 2007 2 months
31 January 2008 and 28 February 2008 1 month
31 January 2008 and 01 March 2008 1 month


"Rick Rothstein (MVP - VB)" wrote:

I need to calculate the difference in months between 01 October 2007 and 31
December 2007. When i am using the following formula
=MONTH(B3)-MONTH(B2)
i am getting a result of 2 although it should be 3....any ideas...help
please

Well, you could always add one to your formula, but I'm thinking you have a
deeper question hiding in here. Is your start date always the first of the
month? Is your end date always the end of the month? If yes, the "plus one"
fix should be enough. If not, then you need to tell us what answer you would
expect for these...

15 October 2007 and 15 December 2007
30 October 2007 and 01 December 2007
31 January 2008 and 28 February 2008
31 January 2008 and 01 March 2008

I'm sure there are other "defining parameters" that we probably need to ask
you about, but the above would get us started.

Rick

Thanks a lot Rick i had already figured out that i can use the +1 fix...
but doesnt it sound weird that Excel cannot calculate

But you aren't asking Excel to count months... you are asking to subtract
two numbers (that **you** associate as months). Just like 3-1 equals 2 (the
difference) and not 3 (the count of digits involved), that is what your
formula is asking Excel to do... so you have to add the one to get what you
are looking for. If you do any programming, you run into this same situation
with arrays. Say you have an array with indexes of 1, 2 and 3. The upper
bound for the array is 3 and the lower bound for the array is 1... the
difference (3-1=2) is **not** the count of the elements... it is one too
few, so you have to add one to the difference to get the actual count of
elements. This "problem" crops up all the time, in many different areas....
it's just the way our number system works and, so, we are stuck with it.

and yes you are right my start date is always the beginning of
the month and my finish date is always the end of the month...
for your answer to the 4 sample date ranges you sent i would
expect the following answers

15 October 2007 and 15 December 2007 2 months
30 October 2007 and 01 December 2007 2 months
31 January 2008 and 28 February 2008 1 month
31 January 2008 and 01 March 2008 1 month

I wasn't completely clear on your needs here... do you need any help with
adjusting dates to beginning and end of months? By the way, I was surprised
at your answers to my date range questions above... if you adjust those
dates to the beginning and end of their respective months, wouldn't your
answers be 1 greater than what you showed? Or did your earlier comment mean
to imply you would never see intermediated dates like I proposed?

Rick

Hi Rick,

Thanks for the explanation....your answer did the trick and i solved the
problem, but another mess i have landed in now.....can you check if anything
you can suggest to help me out on this one

i am using the formula
=IF(B14<F14,DATE(G14,H14+J14,I14),DATE(C14,D14+J14 ,E14))

to add on months (calculated from the previous formula) to a date but if i
add the date in the format 31-dec-2005, i cannot get it hence i have to enter
dates in seperate columns with the Year in one column, date in one column and
month in one column....as my date is not constant this has made my worksheet
look extremely unprofessional....any help!!!!!

thanks once again




"Rick Rothstein (MVP - VB)" wrote:

Thanks a lot Rick i had already figured out that i can use the +1 fix...
but doesnt it sound weird that Excel cannot calculate

But you aren't asking Excel to count months... you are asking to subtract
two numbers (that **you** associate as months). Just like 3-1 equals 2 (the
difference) and not 3 (the count of digits involved), that is what your
formula is asking Excel to do... so you have to add the one to get what you
are looking for. If you do any programming, you run into this same situation
with arrays. Say you have an array with indexes of 1, 2 and 3. The upper
bound for the array is 3 and the lower bound for the array is 1... the
difference (3-1=2) is **not** the count of the elements... it is one too
few, so you have to add one to the difference to get the actual count of
elements. This "problem" crops up all the time, in many different areas....
it's just the way our number system works and, so, we are stuck with it.

and yes you are right my start date is always the beginning of
the month and my finish date is always the end of the month...
for your answer to the 4 sample date ranges you sent i would
expect the following answers

15 October 2007 and 15 December 2007 2 months
30 October 2007 and 01 December 2007 2 months
31 January 2008 and 28 February 2008 1 month
31 January 2008 and 01 March 2008 1 month

I wasn't completely clear on your needs here... do you need any help with
adjusting dates to beginning and end of months? By the way, I was surprised
at your answers to my date range questions above... if you adjust those
dates to the beginning and end of their respective months, wouldn't your
answers be 1 greater than what you showed? Or did your earlier comment mean
to imply you would never see intermediated dates like I proposed?

Rick

=IF(B14<F14,DATE(YEAR(G14),MONTH(G14)+J14,DAY(G14) ),DATE(YEAR(C14),MONTH(C14)+J14,DAY(C14)))
will let you put a date in G14 & C14, instead of splitting it between G/H/J
and C/D/E
--
David Biddulph

"Charlie" wrote in message
...
Hi Rick,

Thanks for the explanation....your answer did the trick and i solved the
problem, but another mess i have landed in now.....can you check if
anything
you can suggest to help me out on this one

i am using the formula
=IF(B14<F14,DATE(G14,H14+J14,I14),DATE(C14,D14+J14 ,E14))

to add on months (calculated from the previous formula) to a date but if i
add the date in the format 31-dec-2005, i cannot get it hence i have to
enter
dates in seperate columns with the Year in one column, date in one column
and
month in one column....as my date is not constant this has made my
worksheet
look extremely unprofessional....any help!!!!!

thanks once again




"Rick Rothstein (MVP - VB)" wrote:

Thanks a lot Rick i had already figured out that i can use the +1
fix...
but doesnt it sound weird that Excel cannot calculate

But you aren't asking Excel to count months... you are asking to subtract
two numbers (that **you** associate as months). Just like 3-1 equals 2
(the
difference) and not 3 (the count of digits involved), that is what your
formula is asking Excel to do... so you have to add the one to get what
you
are looking for. If you do any programming, you run into this same
situation
with arrays. Say you have an array with indexes of 1, 2 and 3. The upper
bound for the array is 3 and the lower bound for the array is 1... the
difference (3-1=2) is **not** the count of the elements... it is one too
few, so you have to add one to the difference to get the actual count of
elements. This "problem" crops up all the time, in many different
areas....
it's just the way our number system works and, so, we are stuck with it.

and yes you are right my start date is always the beginning of
the month and my finish date is always the end of the month...
for your answer to the 4 sample date ranges you sent i would
expect the following answers

15 October 2007 and 15 December 2007 2 months
30 October 2007 and 01 December 2007 2 months
31 January 2008 and 28 February 2008 1 month
31 January 2008 and 01 March 2008 1 month

I wasn't completely clear on your needs here... do you need any help with
adjusting dates to beginning and end of months? By the way, I was
surprised
at your answers to my date range questions above... if you adjust those
dates to the beginning and end of their respective months, wouldn't your
answers be 1 greater than what you showed? Or did your earlier comment
mean
to imply you would never see intermediated dates like I proposed?

Rick