I have the need to create a formula that calculates the number of months
between cell B3 and C3 then divide that number by the amount in cell A1 then
find the €œstart date€� month and put the value for each month until the "end
date" is reached. I hope this makes since, and I appreciate any help you can
give me!!

1 A B C D E F
2 Amount start date end date Jan-07 Feb-07 Mar-07
3 $ 100,000 Feb-07 Sep-07

Thanks,
Charles

Will any of the date periods span into the next year?

Biff

"Charles" wrote in message
...
I have the need to create a formula that calculates the number of months
between cell B3 and C3 then divide that number by the amount in cell A1
then
find the "start date" month and put the value for each month until the
"end
date" is reached. I hope this makes since, and I appreciate any help you
can
give me!!

1 A B C D E F
2 Amount start date end date Jan-07 Feb-07 Mar-07
3 $ 100,000 Feb-07 Sep-07

Thanks,
Charles

Yes, it is for a 3 year forecast, theoretically there could also be some that
goes past the three years therefore that amount would not show up under any
date in the forecast.

"T. Valko" wrote:

Will any of the date periods span into the next year?

Biff

"Charles" wrote in message
...
I have the need to create a formula that calculates the number of months
between cell B3 and C3 then divide that number by the amount in cell A1
then
find the "start date" month and put the value for each month until the
"end
date" is reached. I hope this makes since, and I appreciate any help you
can
give me!!

1 A B C D E F
2 Amount start date end date Jan-07 Feb-07 Mar-07
3 $ 100,000 Feb-07 Sep-07

Thanks,
Charles

Ok, if I understand you that means if the end date is greater than the last
date in row 2 no forecast is calculated.

It may be easier to show you how I did this in a sample file.

Sample file:

Charles.xls 16kb

http://cjoint.com/?fxtzLpjmPc

I only plotted through the end of 2007. Note how I use the full date. I
entered the formula in cell D3 then copied across then down.

Biff

"Charles" wrote in message
...
Yes, it is for a 3 year forecast, theoretically there could also be some
that
goes past the three years therefore that amount would not show up under
any
date in the forecast.

"T. Valko" wrote:

Will any of the date periods span into the next year?

Biff

"Charles" wrote in message
...
I have the need to create a formula that calculates the number of months
between cell B3 and C3 then divide that number by the amount in cell A1
then
find the "start date" month and put the value for each month until the
"end
date" is reached. I hope this makes since, and I appreciate any help
you
can
give me!!

1 A B C D E F
2 Amount start date end date Jan-07 Feb-07 Mar-07
3 $ 100,000 Feb-07 Sep-07

Thanks,
Charles

Wow very nice formula, not being a programmer I could have never even got
close. I do have one question, I think I miss communicated the part of if the
amount goes past the forecast I still need it to put the amount for the
months on the forecast and the overflow amount will not be shown. Is that
an easy fix?
I really appreciate your help!
Charles


"T. Valko" wrote:

Ok, if I understand you that means if the end date is greater than the last
date in row 2 no forecast is calculated.

It may be easier to show you how I did this in a sample file.

Sample file:

Charles.xls 16kb

http://cjoint.com/?fxtzLpjmPc

I only plotted through the end of 2007. Note how I use the full date. I
entered the formula in cell D3 then copied across then down.

Biff

"Charles" wrote in message
...
Yes, it is for a 3 year forecast, theoretically there could also be some
that
goes past the three years therefore that amount would not show up under
any
date in the forecast.

"T. Valko" wrote:

Will any of the date periods span into the next year?

Biff

"Charles" wrote in message
...
I have the need to create a formula that calculates the number of months
between cell B3 and C3 then divide that number by the amount in cell A1
then
find the "start date" month and put the value for each month until the
"end
date" is reached. I hope this makes since, and I appreciate any help
you
can
give me!!

1 A B C D E F
2 Amount start date end date Jan-07 Feb-07 Mar-07
3 $ 100,000 Feb-07 Sep-07

Thanks,
Charles

Wow very nice formula, not being a programmer I could have never even got
close. I do have one question, I think I miss communicated the part of if the
amount goes past the forecast I still need it to put the amount for the
months on the forecast and the overflow amount will not be shown. Is that
an easy fix?
I really appreciate your help!
Charles


"T. Valko" wrote:

Ok, if I understand you that means if the end date is greater than the last
date in row 2 no forecast is calculated.

It may be easier to show you how I did this in a sample file.

Sample file:

Charles.xls 16kb

http://cjoint.com/?fxtzLpjmPc

I only plotted through the end of 2007. Note how I use the full date. I
entered the formula in cell D3 then copied across then down.

Biff

"Charles" wrote in message
...
Yes, it is for a 3 year forecast, theoretically there could also be some
that
goes past the three years therefore that amount would not show up under
any
date in the forecast.

"T. Valko" wrote:

Will any of the date periods span into the next year?

Biff

"Charles" wrote in message
...
I have the need to create a formula that calculates the number of months
between cell B3 and C3 then divide that number by the amount in cell A1
then
find the "start date" month and put the value for each month until the
"end
date" is reached. I hope this makes since, and I appreciate any help
you
can
give me!!

1 A B C D E F
2 Amount start date end date Jan-07 Feb-07 Mar-07
3 $ 100,000 Feb-07 Sep-07

Thanks,
Charles

Wow very nice formula, not being a programmer I could have never even got
close. I do have one question, I think I miss communicated the part of if the
amount goes past the forecast I still need it to put the amount for the
months on the forecast and the overflow amount will not be shown. Is that
an easy fix?
I really appreciate your help!
Charles


"T. Valko" wrote:

Ok, if I understand you that means if the end date is greater than the last
date in row 2 no forecast is calculated.

It may be easier to show you how I did this in a sample file.

Sample file:

Charles.xls 16kb

http://cjoint.com/?fxtzLpjmPc

I only plotted through the end of 2007. Note how I use the full date. I
entered the formula in cell D3 then copied across then down.

Biff

"Charles" wrote in message
...
Yes, it is for a 3 year forecast, theoretically there could also be some
that
goes past the three years therefore that amount would not show up under
any
date in the forecast.

"T. Valko" wrote:

Will any of the date periods span into the next year?

Biff

"Charles" wrote in message
...
I have the need to create a formula that calculates the number of months
between cell B3 and C3 then divide that number by the amount in cell A1
then
find the "start date" month and put the value for each month until the
"end
date" is reached. I hope this makes since, and I appreciate any help
you
can
give me!!

1 A B C D E F
2 Amount start date end date Jan-07 Feb-07 Mar-07
3 $ 100,000 Feb-07 Sep-07

Thanks,
Charles

I still need it to put the amount for the months on the
forecast and the overflow amount will not be shown.

Using the sample file, does that mean on row 4 where the end date is
2/1/2008 the forecast will fill in from 7/1/2007 to 12/1/2007? If that's the
case would the forecast still divide the amount by the total months from
7/12007 to 2/1/2008 or would it just divide by the number of months from
7/1/2007 to 12/1/2007?

Better yet, using the sample file, tell me what the results should be on row
4. I'll be able to figure it out from there!

Biff

"Charles" wrote in message
...
Wow very nice formula, not being a programmer I could have never even got
close. I do have one question, I think I miss communicated the part of if
the
amount goes past the forecast I still need it to put the amount for the
months on the forecast and the overflow amount will not be shown. Is
that
an easy fix?
I really appreciate your help!
Charles


"T. Valko" wrote:

Ok, if I understand you that means if the end date is greater than the
last
date in row 2 no forecast is calculated.

It may be easier to show you how I did this in a sample file.

Sample file:

Charles.xls 16kb

http://cjoint.com/?fxtzLpjmPc

I only plotted through the end of 2007. Note how I use the full date. I
entered the formula in cell D3 then copied across then down.

Biff

"Charles" wrote in message
...
Yes, it is for a 3 year forecast, theoretically there could also be
some
that
goes past the three years therefore that amount would not show up under
any
date in the forecast.

"T. Valko" wrote:

Will any of the date periods span into the next year?

Biff

"Charles" wrote in message
...
I have the need to create a formula that calculates the number of
months
between cell B3 and C3 then divide that number by the amount in cell
A1
then
find the "start date" month and put the value for each month until
the
"end
date" is reached. I hope this makes since, and I appreciate any help
you
can
give me!!

1 A B C D E F
2 Amount start date end date Jan-07 Feb-07 Mar-07
3 $ 100,000 Feb-07 Sep-07

Thanks,
Charles

The amount for line 4 in 2007 should be 15,625. And again thanks for your
help it is really appreciated.

"T. Valko" wrote:

I still need it to put the amount for the months on the
forecast and the overflow amount will not be shown.

Using the sample file, does that mean on row 4 where the end date is
2/1/2008 the forecast will fill in from 7/1/2007 to 12/1/2007? If that's the
case would the forecast still divide the amount by the total months from
7/12007 to 2/1/2008 or would it just divide by the number of months from
7/1/2007 to 12/1/2007?

Better yet, using the sample file, tell me what the results should be on row
4. I'll be able to figure it out from there!

Biff

"Charles" wrote in message
...
Wow very nice formula, not being a programmer I could have never even got
close. I do have one question, I think I miss communicated the part of if
the
amount goes past the forecast I still need it to put the amount for the
months on the forecast and the overflow amount will not be shown. Is
that
an easy fix?
I really appreciate your help!
Charles


"T. Valko" wrote:

Ok, if I understand you that means if the end date is greater than the
last
date in row 2 no forecast is calculated.

It may be easier to show you how I did this in a sample file.

Sample file:

Charles.xls 16kb

http://cjoint.com/?fxtzLpjmPc

I only plotted through the end of 2007. Note how I use the full date. I
entered the formula in cell D3 then copied across then down.

Biff

"Charles" wrote in message
...
Yes, it is for a 3 year forecast, theoretically there could also be
some
that
goes past the three years therefore that amount would not show up under
any
date in the forecast.

"T. Valko" wrote:

Will any of the date periods span into the next year?

Biff

"Charles" wrote in message
...
I have the need to create a formula that calculates the number of
months
between cell B3 and C3 then divide that number by the amount in cell
A1
then
find the "start date" month and put the value for each month until
the
"end
date" is reached. I hope this makes since, and I appreciate any help
you
can
give me!!

1 A B C D E F
2 Amount start date end date Jan-07 Feb-07 Mar-07
3 $ 100,000 Feb-07 Sep-07

Thanks,
Charles

Ok, that actually makes it a little easier!

Enter this formula in D3:

=IF(AND(D$2=$B3,D$2<=$C3),$A3/(DATEDIF($B3,$C3,"m")+1),"")

Copied across then down as needed.

Biff

"Charles" wrote in message
...
The amount for line 4 in 2007 should be 15,625. And again thanks for your
help it is really appreciated.

"T. Valko" wrote:

I still need it to put the amount for the months on the
forecast and the overflow amount will not be shown.

Using the sample file, does that mean on row 4 where the end date is
2/1/2008 the forecast will fill in from 7/1/2007 to 12/1/2007? If that's
the
case would the forecast still divide the amount by the total months from
7/12007 to 2/1/2008 or would it just divide by the number of months from
7/1/2007 to 12/1/2007?

Better yet, using the sample file, tell me what the results should be on
row
4. I'll be able to figure it out from there!

Biff

"Charles" wrote in message
...
Wow very nice formula, not being a programmer I could have never even
got
close. I do have one question, I think I miss communicated the part of
if
the
amount goes past the forecast I still need it to put the amount for the
months on the forecast and the overflow amount will not be shown. Is
that
an easy fix?
I really appreciate your help!
Charles


"T. Valko" wrote:

Ok, if I understand you that means if the end date is greater than the
last
date in row 2 no forecast is calculated.

It may be easier to show you how I did this in a sample file.

Sample file:

Charles.xls 16kb

http://cjoint.com/?fxtzLpjmPc

I only plotted through the end of 2007. Note how I use the full date.
I
entered the formula in cell D3 then copied across then down.

Biff

"Charles" wrote in message
...
Yes, it is for a 3 year forecast, theoretically there could also be
some
that
goes past the three years therefore that amount would not show up
under
any
date in the forecast.

"T. Valko" wrote:

Will any of the date periods span into the next year?

Biff

"Charles" wrote in message
...
I have the need to create a formula that calculates the number of
months
between cell B3 and C3 then divide that number by the amount in
cell
A1
then
find the "start date" month and put the value for each month
until
the
"end
date" is reached. I hope this makes since, and I appreciate any
help
you
can
give me!!

1 A B C D E F
2 Amount start date end date Jan-07 Feb-07 Mar-07
3 $ 100,000 Feb-07 Sep-07

Thanks,
Charles

Great formula!! Thanks for your help...

"T. Valko" wrote:

Ok, that actually makes it a little easier!

Enter this formula in D3:

=IF(AND(D$2=$B3,D$2<=$C3),$A3/(DATEDIF($B3,$C3,"m")+1),"")

Copied across then down as needed.

Biff

"Charles" wrote in message
...
The amount for line 4 in 2007 should be 15,625. And again thanks for your
help it is really appreciated.

"T. Valko" wrote:

I still need it to put the amount for the months on the
forecast and the overflow amount will not be shown.

Using the sample file, does that mean on row 4 where the end date is
2/1/2008 the forecast will fill in from 7/1/2007 to 12/1/2007? If that's
the
case would the forecast still divide the amount by the total months from
7/12007 to 2/1/2008 or would it just divide by the number of months from
7/1/2007 to 12/1/2007?

Better yet, using the sample file, tell me what the results should be on
row
4. I'll be able to figure it out from there!

Biff

"Charles" wrote in message
...
Wow very nice formula, not being a programmer I could have never even
got
close. I do have one question, I think I miss communicated the part of
if
the
amount goes past the forecast I still need it to put the amount for the
months on the forecast and the overflow amount will not be shown. Is
that
an easy fix?
I really appreciate your help!
Charles


"T. Valko" wrote:

Ok, if I understand you that means if the end date is greater than the
last
date in row 2 no forecast is calculated.

It may be easier to show you how I did this in a sample file.

Sample file:

Charles.xls 16kb

http://cjoint.com/?fxtzLpjmPc

I only plotted through the end of 2007. Note how I use the full date.
I
entered the formula in cell D3 then copied across then down.

Biff

"Charles" wrote in message
...
Yes, it is for a 3 year forecast, theoretically there could also be
some
that
goes past the three years therefore that amount would not show up
under
any
date in the forecast.

"T. Valko" wrote:

Will any of the date periods span into the next year?

Biff

"Charles" wrote in message
...
I have the need to create a formula that calculates the number of
months
between cell B3 and C3 then divide that number by the amount in
cell
A1
then
find the "start date" month and put the value for each month
until
the
"end
date" is reached. I hope this makes since, and I appreciate any
help
you
can
give me!!

1 A B C D E F
2 Amount start date end date Jan-07 Feb-07 Mar-07
3 $ 100,000 Feb-07 Sep-07

Thanks,
Charles

You're welcome. Thanks for the feedback!

Biff

"Charles" wrote in message
...
Great formula!! Thanks for your help...

"T. Valko" wrote:

Ok, that actually makes it a little easier!

Enter this formula in D3:

=IF(AND(D$2=$B3,D$2<=$C3),$A3/(DATEDIF($B3,$C3,"m")+1),"")

Copied across then down as needed.

Biff

"Charles" wrote in message
...
The amount for line 4 in 2007 should be 15,625. And again thanks for
your
help it is really appreciated.

"T. Valko" wrote:

I still need it to put the amount for the months on the
forecast and the overflow amount will not be shown.

Using the sample file, does that mean on row 4 where the end date is
2/1/2008 the forecast will fill in from 7/1/2007 to 12/1/2007? If
that's
the
case would the forecast still divide the amount by the total months
from
7/12007 to 2/1/2008 or would it just divide by the number of months
from
7/1/2007 to 12/1/2007?

Better yet, using the sample file, tell me what the results should be
on
row
4. I'll be able to figure it out from there!

Biff

"Charles" wrote in message
...
Wow very nice formula, not being a programmer I could have never
even
got
close. I do have one question, I think I miss communicated the part
of
if
the
amount goes past the forecast I still need it to put the amount for
the
months on the forecast and the overflow amount will not be shown.
Is
that
an easy fix?
I really appreciate your help!
Charles


"T. Valko" wrote:

Ok, if I understand you that means if the end date is greater than
the
last
date in row 2 no forecast is calculated.

It may be easier to show you how I did this in a sample file.

Sample file:

Charles.xls 16kb

http://cjoint.com/?fxtzLpjmPc

I only plotted through the end of 2007. Note how I use the full
date.
I
entered the formula in cell D3 then copied across then down.

Biff

"Charles" wrote in message
...
Yes, it is for a 3 year forecast, theoretically there could also
be
some
that
goes past the three years therefore that amount would not show up
under
any
date in the forecast.

"T. Valko" wrote:

Will any of the date periods span into the next year?

Biff

"Charles" wrote in message
...
I have the need to create a formula that calculates the number
of
months
between cell B3 and C3 then divide that number by the amount
in
cell
A1
then
find the "start date" month and put the value for each month
until
the
"end
date" is reached. I hope this makes since, and I appreciate
any
help
you
can
give me!!

1 A B C D E F
2 Amount start date end date Jan-07 Feb-07 Mar-07
3 $ 100,000 Feb-07 Sep-07

Thanks,
Charles

Dear T.Valko

Is there any way you can send or link me to the file you were referring to?
I have the same question as Charlie, only my start and end dates span more
than one calendar year.

"T. Valko" wrote:

Ok, if I understand you that means if the end date is greater than the last
date in row 2 no forecast is calculated.

It may be easier to show you how I did this in a sample file.

Sample file:

Charles.xls 16kb

http://cjoint.com/?fxtzLpjmPc

I only plotted through the end of 2007. Note how I use the full date. I
entered the formula in cell D3 then copied across then down.

Biff

"Charles" wrote in message
...
Yes, it is for a 3 year forecast, theoretically there could also be some
that
goes past the three years therefore that amount would not show up under
any
date in the forecast.

"T. Valko" wrote:

Will any of the date periods span into the next year?

Biff

"Charles" wrote in message
...
I have the need to create a formula that calculates the number of months
between cell B3 and C3 then divide that number by the amount in cell A1
then
find the "start date" month and put the value for each month until the
"end
date" is reached. I hope this makes since, and I appreciate any help
you
can
give me!!

1 A B C D E F
2 Amount start date end date Jan-07 Feb-07 Mar-07
3 $ 100,000 Feb-07 Sep-07

Thanks,
Charles