Hi. I am attempting to calendarize quantities across 12 months using some
type of automated function (as the start dates and quantities will change per
project and I don't want this to be a manual function). I've gotten as far as
"telling" the application that if a project duration begins in a particular
month, to put quantities in the start month...but the kicker (problem) is
that I don't know how to make the application spread the quantities over the
other months (duration) of the project...for instance, a project begins in
April and ends in June. I need to see 200 pages per month (project totals 600
pages with equal amounts of pages being completed over the 3-month period);
the program reads that I need 200 pages in April, but I don't know how to get
the rest of the pages for May and June to read--and then end at June...can
anyone help?

Hi. I am attempting to calendarize quantities across 12 months using
some type of automated function (as the start dates and quantities
will change per project and I don't want this to be a manual
function). I've gotten as far as "telling" the application that if a
project duration begins in a particular month, to put quantities in
the start month...but the kicker (problem) is that I don't know how to
make the application spread the quantities over the other months
(duration) of the project...for instance, a project begins in April
and ends in June. I need to see 200 pages per month (project totals
600 pages with equal amounts of pages being completed over the 3-month
period); the program reads that I need 200 pages in April, but I don't
know how to get the rest of the pages for May and June to read--and
then end at June...can anyone help?

The following suggests one way to proceed.

In column A put
Start
Months
Pages
Aug
Sep
Oct
Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul

In B1 put
Nov

In B2 put
4

In B3 put
600

In B4 put
=IF(B$1=$A4,B$3/B$2,"")

In B5 put
=IF(B$1=$A5,B$3/B$2,IF(B4="","",
IF(COUNT(B$4:B4)<B$2,B$3/B$2,"")))
and copy B5 down to B15.

This example shows starting in Nov for 4 months with 600 pages total. For
other examples, change B1, B2, and B3.

This prorates based on the number of days in the month in the project range.

Start End Pages 1/31/08 2/29/08 3/31/08 4/30/08
01/02/08 05/20/08 300 65.2 63.0 67.4 65.2
02/05/08 04/28/08 200 61.0 75.6 68.3
02/16/08 03/12/08 500 291.7 250.0
03/31/08 04/30/08 700 24.1 724.1


The formula in D3 is

=IF(AND(D$1=$A2-1,DATEVALUE("12/31/07")<=$B2),$C2/($B2-$A2-1)*(MIN(D$1,$B2)-MAX(12/31/8,$A2-1)),"")

The formula in E3 is

=IF(AND(E$1$A2-1,D$1<$B2),$C2/($B2-$A2-1)*(MIN(E$1,$B2)-MAX(D$1,$A2-1)),"")

This spreads evenly among months even if only one day falls in the project
range.

Start End Pages 1/31/08 2/29/08 3/31/08 4/30/08
01/02/08 05/20/08 300 60.0 60.0 60.0 60.0
02/05/08 04/28/08 200 66.7 66.7 66.7
02/16/08 03/01/08 500 250.0 250.0
03/31/08 04/30/08 700 350.0 350.0

The formula in D3 is
=IF(AND(MONTH(D$1)<=MONTH($B2),MONTH(D$1)=MONTH($ A2)),$C2/(MONTH($B2)-MONTH($A2)+1),"")

Hope this helps.


"harcourt" wrote:

Hi. I am attempting to calendarize quantities across 12 months using some
type of automated function (as the start dates and quantities will change per
project and I don't want this to be a manual function). I've gotten as far as
"telling" the application that if a project duration begins in a particular
month, to put quantities in the start month...but the kicker (problem) is
that I don't know how to make the application spread the quantities over the
other months (duration) of the project...for instance, a project begins in
April and ends in June. I need to see 200 pages per month (project totals 600
pages with equal amounts of pages being completed over the 3-month period);
the program reads that I need 200 pages in April, but I don't know how to get
the rest of the pages for May and June to read--and then end at June...can
anyone help?