I am trying to determine the number of months that are spanned in a
given period. For example, the number of months spanned in the period
16 Jun 06 to 17 Jul 06 is 2.

All the functions in Excel that I have found give the result as 31 days
or 1 month.

I am trying to avoid creating a lookup table that lists each month of
each year as the periods I need to look at will go up to and beyond
2018 (without me knowing) so ideally, the process needs to automated so
there are no tables to maintain.

I'd be grateful if anyone could advise how I can go about this.

Thanks

One way:

=IF(MONTH(B1)-MONTH(A1)=0,MONTH(B1)-MONTH(A1)+1,MONTH(B1)-MONTH(A1)+1+12*(YEAR(B1)-YEAR(A1)))

Note that the date in column B *must* be greater than or equal to the date
in column A

Regards

Trevor


wrote in message
oups.com...
I am trying to determine the number of months that are spanned in a
given period. For example, the number of months spanned in the period
16 Jun 06 to 17 Jul 06 is 2.

All the functions in Excel that I have found give the result as 31 days
or 1 month.

I am trying to avoid creating a lookup table that lists each month of
each year as the periods I need to look at will go up to and beyond
2018 (without me knowing) so ideally, the process needs to automated so
there are no tables to maintain.

I'd be grateful if anyone could advise how I can go about this.

Thanks

Thanks for this.

It works a treat if I'm looking at a 12 month period. But it won't work
for a period greater than that - unless maybe I have done something
wrong....

Thanks for the base logic - I'll try playing around with it some more.

Try

=DATEDIF(A1,B1,"m")+(DAY(A1)DAY(B1))+

--
daddylongleg
-----------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...fo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=52379

Did you try it with a period greater than 12 months ? It works for me.
That's the point of the +12*(YEAR(B1)-YEAR(A1)) part of the formula ... it
adds 12 for each change of year.


"happywitchie" wrote in message
ups.com...
Thanks for this.

It works a treat if I'm looking at a 12 month period. But it won't work
for a period greater than that - unless maybe I have done something
wrong....

Thanks for the base logic - I'll try playing around with it some more.

Thanks very much - this does just what I need.

Cheers!

Hi Trevor

Yes, I did it try with all kinds of periods and it doesn't work on
greater than 12. I'm not sure what could be the trouble as I tried both
cutting and pasting the formula as you wrote it here and typing it in
myself. I'm using Excel 2003 on XP if that makes a difference. But
the formula that daddylonglegs just gave works well for me.

Thanks for your help.

Hi Trevor,

The TRUE part of your IF formula doesn't always give the correc
result, i.e. where MONTH(B1)=MONTH(A1) but YEAR(A1) and YEAR(B1) ar
different. E.g.

A1 is 01/01/2005
B1 is 02/02/2006

result should be 14

=IF(MONTH(B1)-MONTH(A1)=0,MONTH(B1)-MONTH(A1)+1,MONTH(B1)-MONTH(A1)+1+12*(YEAR(B1)-YEAR(A1)))

gives 2.

You only actually need the final part of your formula, that will wor
in all cases (where date in B1 is later than or equal to date in A1)
i.e.

=MONTH(B1)-MONTH(A1)+12*(YEAR(B1)-YEAR(A1))+

--
daddylongleg
-----------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...fo&userid=3048
View this thread: http://www.excelforum.com/showthread.php?threadid=52379

Thanks for working it through for me ;-(

I went through a few iterations to get to that formula as I realised I
needed to cater for going across the end of year(s) boundary ... just didn't
test ALL the variations.

It worked for everything I DID test ;-)

Oh well, you live and learn ... even though sometimes you embarrass yourself
!

Your "revision" of my formula was much neater ... but your own was better.

Regards ... and thanks again

Trevor


"daddylonglegs"
wrote in message
news:daddylonglegs.24vjtb_1142700602.2947@excelfor um-nospam.com...

Hi Trevor,

The TRUE part of your IF formula doesn't always give the correct
result, i.e. where MONTH(B1)=MONTH(A1) but YEAR(A1) and YEAR(B1) are
different. E.g.

A1 is 01/01/2005
B1 is 02/02/2006

result should be 14

=IF(MONTH(B1)-MONTH(A1)=0,MONTH(B1)-MONTH(A1)+1,MONTH(B1)-MONTH(A1)+1+12*(YEAR(B1)-YEAR(A1)))

gives 2.

You only actually need the final part of your formula, that will work
in all cases (where date in B1 is later than or equal to date in A1),
i.e.

=MONTH(B1)-MONTH(A1)+12*(YEAR(B1)-YEAR(A1))+1


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=523795