Month Calculation

Hello,

I am trying to create a formula that will allow me to calculate the # of months between two dates. The catch being that the two dates are contained in a single cell and they are displayed as mm/yy, rather than mm/dd/yy (i.e. 7/07-9/07, which should = 3 months). Also, months that are not in the fiscal year (7/1/2007-6/30/2008) should not be included in the count (i.e. 4/08-3/09 should = 3 months). Furthermore, any date ranges that are not within the 7/07-6/08 range should = 0 months (i.e. 7/05-6/06).

This is the formula I have come up with, however, I cannot add any more levels, which forces me to ignore a few bugs that are inherent.

=YEARFRAC(IF(VALUE("7/1/2007")>=VALUE(SUBSTITUTE(IF(ISBLANK(K5),"xxxxx",LEFT(K5,FIND("-",K5)-1)),"/","/01/20",1)),VALUE("7/1/2007"),VALUE(SUBSTITUTE(IF(ISBLANK(K5),"xxxxx",LEFT(K5,FIND("-",K5)-1)),"/","/01/",1))),IF(VALUE("6/30/2008")<=VALUE(SUBSTITUTE(SUBSTITUTE(IF(ISBLANK(K5),"xxxxx",RIGHT(K5,FIND("-",K5)-1)),"/","/28/",1),"-",,1)),VALUE("6/30/2008"),VALUE(SUBSTITUTE(SUBSTITUTE(IF(ISBLANK(K5),"xxxxx",RIGHT(K5,FIND("-",K5)-1)),"/","/28/",1),"-",,1))))*12

K5=the cell with date range.

Can anyone help me come up with a code that works everytime and is a bit more simple?

It would be greatly appreciated!

Thanks in advance.

(P.S. sorry for the title misspelling >.<)

I made some minor changes just with the >/</= signs, the dates, and the FALSE return value, but other then that, it works great - thanks!

Here's the formula with the minor amendments:

=IF(AND(--SUBSTITUTE((LEFT(K5,FIND("-",K5)-1)),"/","/1/")<=--"6/30/08",--SUBSTITUTE(MID(K5,FIND("-",K5)+1,255),"/","/28/")>--"7/1/07"),DATEDIF(MAX(--"7/1/07",--SUBSTITUTE((LEFT(K5,FIND("-",K5)-1)),"/","/1/")),MIN(--"6/30/08",--SUBSTITUTE(MID(K5,FIND("-",K5)+1,255),"/","/28/")),"M")+1,0)

Also, is there anyway to remove singular spaces like with the TRIM() function? The formula returns errors for a small portion of the cells because there's a space in between the date and the hyphen. Not a big problem, but just curious.

Anyways, thanks again!

Perfection

Thank you.