dividing numbers among dates

[Kevin]

I'd like to write an expression that divides up labor
hours per day, then totals them into the appropriate month.

For example: An item requires 80 labor hours to complete,
starting 1/26/04 and completing 2/6/04. 8 hours would be
alloted for each of the 10 business days. When totalled
by report, it would show January/40 hrs and February/40hrs.

I'm thinking I need to utilize the DateDiff function, but
not sure how to get it to divide equally amongst each day
(or week) once the DateDiff is achieved. I hope this all
makes sense. I greatly appreciate any suggestions!

 

[Tom Ellison]

Dear Kevin:

If you had a separate table with dates and hours, something like this:

01/23/04 8
01/26/04 8
01/27/04 8
01/28/04 8
01/29/04 8
01/30/04 8
02/02/04 8
02/03/04 8
02/04/04 8
02/05/04 8
02/06/04 8
02/09/04 8

You could then find the first however-many days, starting from
1/26/04, whose total adds up to the 80 hours or MORE. That is, if the
target number of hours were 82 hours, you'd need 2 more hours from
02/09/04.

You would need a running sum of the number of hours given the starting
date. When you pick the minimum value of this running sum where the
running sum is greater than or equal to the number of hours required,
you should have your result pretty well pegged.

This is not a complete solution to your problem, just an "algorithm"
that should produce what you want. The above table give you
flexibility to schedule half days, add a second shift, and exclude
holidays. Excluding days already scheduled for other projects would
be a relatively easy extension of this.

I'm also having an idea about improving the performance. If the
running sum were stored in the table, you could subtract that value
for the starting day from the prospective ending dates, rather than
perform the running sum calculation every time.

Now it's your turn to respond to the "brain-storming" phase of finding
the solution

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts