Your suggestion is far less messy than what I've been trying to work with. It
seems to work great except for any term that is less than 12 months. For
example, if I have a term of 04/01/06 thru 06/30/06, it results 12 instead of
3. ???

"Rowan Drummond" wrote:

Messy but it seems to work. In C2 enter:
=IF($A2C$1,0,IF($B2C$1,MIN(DATEDIF($A2,C$1+1,"m" ),12),MAX(12-DATEDIF($B2,C$1+1,"m"),0)))
and copy accross.

Hope this helps
Rowan

Corey wrote:
Perhaps I need to clarify a little more.

Header columns would be (fiscal year ending):

C1 = 06/30/05
D1 = 06/30/06
E1 = 06/30/07
F1 = 06/30/08
G1 = 06/30/08

From the example I stated, I would like to just input the following:

A2 = 11/01/05 (beginning date)
B2 = 10/31/08 (ending date)

Then get the following results:

C2 = 0 (months)
D2 = 8
E2 = 12
F2 = 12
G2 = 4

This would automatically allocate the inputted term in the proper periods.
However, the formula would have to accomodate any date range I put in as I
have many with different terms. Perhaps a modification to the suggestions
already made. ???

Thanks in advance.


"Corey" wrote:


Hello. I've tried this a couple of times in the past and have been
unsuccessful and was hoping someone out there has already wrote a formula for
it. The question is regarding a date driven schedule. For instance, a lease
might be from 11/1/05 thru 10/31/08. I have a full schedule with columns for
the periods ending 06/30/05, 06/30/06, 06/30/07 and so on (June 30
year-ends). Is there a formula I could use to calculate the amount of months
that will fall within these buckets? I'll add to this throughout the year and
was hoping to just put in the time frame and it would automatically populate
the months for each year. I'm stuck on the if < or for the beginning and
ending months. Any help is appreciated. Thanks!

Corey