Date driven formula/worksheet

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

To calculate the number of months between two dates, you can use:

=(year(enddate)-year(startdate))*12+(month(enddate)-month(startdate))

As long as the day in each date is consistent, this will be accurate. However,
it can be off if one date is the 1st of the month, and the other is the last
day.

--
Regards,
Fred


"Corey" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Corey,

DATEDIF is the usual function to do this, like

=DATEDIF(J1,J2,"m")

Be aware that in the example that you gave it will return 35. I am not sure
whether this is correct in your view, or whether you think it is 36, but the
function returns the number of complete months, so you will need to
determine when and if you add one for this month.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Corey" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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
>

Messy but it seems to work. In C2 enter:
=IF($A2>C$1,0,IF($B2>C$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
>>

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($A2>C$1,0,IF($B2>C$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
> >>
>

Nevermind...it just seemed to work that way for a date that ended on 06/30.
By changing the formula to >= it solved the problem. Thanks so much for
this...I never would have got it.

"Corey" wrote:

> 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($A2>C$1,0,IF($B2>C$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
> > >>
> >

Glad to hear it worked.

Regards
Rowan

Corey wrote:
> Nevermind...it just seemed to work that way for a date that ended on 06/30.
> By changing the formula to >= it solved the problem. Thanks so much for
> this...I never would have got it.
>
> "Corey" wrote:
>
>

Well, I ran into a couple of problems with this formula. For the 200+ rows
I’m applying it in, there’s a handful that aren’t calculating right. For
example, I’ll have a date range of :

10/01/01 thru 02/28/02

And the result for the 06/30/02 column is 8, when it should be 5

Some more are:

09/01/04 thru 05/31/05 = 11 in 06/30/05 column, when it should be 9
01/01/04 thru 01/31/04 = 7 in 06/30/04 column, when it should be 1
02/01/04 thru 03/31/04 = 9 in 06/30/04 column, when it should be 2

I’m not sure where to begin on editing the formula.

Thanks.


"Rowan Drummond" wrote:

> Glad to hear it worked.
>
> Regards
> Rowan
>
> Corey wrote:
> > Nevermind...it just seemed to work that way for a date that ended on 06/30.
> > By changing the formula to >= it solved the problem. Thanks so much for
> > this...I never would have got it.
> >
> > "Corey" wrote:
> >
> >
>

Does anybody have any more suggestions to this one? The formula seems so
close, but I just can't figure it out. Is this thread still being looked at?

Corey

"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
>

Try

=IF($A2>C$1,0,IF($B2>C$1,MIN(DATEDIF($A2,C$1+1,"m"),12),MAX(12-DATEDIF($B2,C
$1+1,"m")-IF($A2>B$1,DATEDIF(B$1,$A2,"m"),0),0)))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Corey" <[email protected]> wrote in message
news:[email protected]...
> Does anybody have any more suggestions to this one? The formula seems so
> close, but I just can't figure it out. Is this thread still being looked
at?
>
> Corey
>
> "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
> >