Calendarize?

[harcourt]

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?

 

[MyVeryOwnSelf]

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.

 

[Erin Searfoss]

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.