HI, I have a problem I want to solve. I do not know if I can solve it
in excel with iteration ( or even VBA). I cannot use MS Project. I
have a spreadsheet with task durations. I also have fixed man hours
per day. I would like to determine which tasks get done on which days.
The first task may not be completed in the first day. Sometimes it
will ( the task's duration varies from week to week, but they are
ordered ). One you finish with one task, you start the next and keep
going.
>From what I can see, this is a bin packing problem, similar to the one
to back up files to cd's, but in this case, I can split the files.
thanks
Heather Foucault

On Jun 27, 3:51 pm, h_wile...@hotmail.com wrote:
> HI, I have a problem I want to solve. I do not know if I can solve it
> in excel with iteration ( or even VBA). I cannot use MS Project. I
> have a spreadsheet with task durations. I also have fixed man hours
> per day. I would like to determine which tasks get done on which days.
> The first task may not be completed in the first day. Sometimes it
> will ( the task's duration varies from week to week, but they are
> ordered ). One you finish with one task, you start the next and keep
> going.>From what I can see, this is a bin packing problem, similar to the one
>
> to back up files to cd's, but in this case, I can split the files.
> thanks
> Heather Foucault

In Sheet1
Have a cell named "MHPD" for man-hours-per-day, and put your value in
it (=8?)

In Sheet2
Place task durations in column A, a count of hours required for each
task
Enter formula =if($A1=0,"",if($A1>MHPD,$A1-MHPD,"DONE")) into B1, copy
it down for each task. This will show time remaining (or DONE) for
each task at the end of Day1.

Copy B1 to C1, and then copy it down for each task. This will show
time remaining (or DONE) for each task at the end of Day2.

Continue copying as desired.

Heather,
If this doesn't help, I hope it gets you thinking along a helpful
line...

Brian Herbert Withun

On Jun 27, 5:05 pm, SpreadsheetBrian <bhwit...@gmail.com> wrote:
> On Jun 27, 3:51 pm, h_wile...@hotmail.com wrote:
>
> > HI, I have a problem I want to solve. I do not know if I can solve it
> > in excel with iteration ( or even VBA). I cannot use MS Project. I
> > have a spreadsheet with task durations. I also have fixed man hours
> > per day. I would like to determine which tasks get done on which days.
> > The first task may not be completed in the first day. Sometimes it
> > will ( the task's duration varies from week to week, but they are
> > ordered ). One you finish with one task, you start the next and keep
> > going.>From what I can see, this is abinpackingproblem, similar to the one
>
> > to back up files to cd's, but in this case, I can split the files.
> > thanks
> > Heather Foucault
>
> In Sheet1
> Have a cell named "MHPD" for man-hours-per-day, and put your value in
> it (=8?)
>
> In Sheet2
> Place task durations in column A, a count of hours required for each
> task
> Enter formula =if($A1=0,"",if($A1>MHPD,$A1-MHPD,"DONE")) into B1, copy
> it down for each task. This will show time remaining (or DONE) for
> each task at the end of Day1.
>
> Copy B1 to C1, and then copy it down for each task. This will show
> time remaining (or DONE) for each task at the end of Day2.
>
> Continue copying as desired.
>
> Heather,
> If this doesn't help, I hope it gets you thinking along a helpful
> line...
>
> Brian Herbert Withun

Perhaps I wasn't clear with what I needed.

Here is the data and the outcome of your formula
36.57 8.57
20.91 DONE
14.98 DONE

with MHPD being 28. But the first task did not get done on the first
day and those hours must carry over into the second day. This just
calculates if a task can be done in one day. I need to calculate on
which day the task is done.

Think of the days as bins and the hours as so many units of each item.
So as above I have 36.57 units of item1, 20.91 units of item2 and
14.98 units of item3. I have to pack the items in order ( item1,
item2, item3 ) and know what bins they are in.

Ok Heather I think I see what you are after,

DEFINE

ColumnA: TaskHours -- values (in hours) of each task, in to-be-
completed order
ColumnB: Days -- a real number of days required to complete the task
in A
ColumnC: Done -- the actual date when the task will be completed

WHERE

ColumnA is your hours data A3=36.57, formatted 0.00
ColumnB is the formula B3=A3/MHPD, formatted 0.0
ColumnC is the formula C3=C2+B3, formatted mmmm d, yyyy

OVERALL

Row1 is your column headings ={"Hours","Days","Done"} // this
represents the three cells, A1, B1, C1
Row2 is a record indicating the start of your calendar {"","",
6/28/2007} // A2, B2, C2
Row3 is your first task ={36.57,=A3/MHPD,=C2+B3} // A3, B3, C3
Row4 is your second task ={20.91,=A4/MHPD,=C3+B4} // ...
Row5 ...
Row6...

RESULT

Hours Days Done
- - June 28, 2007
36.57 1.3 June 29, 2007
20.91 0.7 June 30, 2007
14.98 0.5 June 30, 2007
32.00 1.1 July 1, 2007
4.00 0.1 July 1, 2007
15.20 0.5 July 2, 2007
99.00 3.5 July 5, 2007

Is this closer to what you had hoped for?

Brian Herbert Withun