"Pete_UK" wrote:

Actually, I don't think this is too difficult !!

Thanks Pete. So far, what you've written is very similar to what I've been
working on. The difficulty comes in actually doing the "allocating" of those
fractional times to specific shifts. At the moment, I am trying to do this
with vlookup and match functions (raw data on one tab, lookup table with each
shift/day combo on another tab). I think my problem is I'm thinking more
linearly, like programming. I want to run through the data one line at a
time and increment a "count" next to each shift by the elapsed time for that
shift based on calculations so far. Getting it to do it all at once in
spreadsheet fashion is proving challenging (to me, at least).

Please do let us know if you make any progress on getting this into excel
formulas, and as Biff said, a simple one-formula answer would be fantastic.
At the moment, I'm spreading out into dozens of columns to the right of my
data to keep track of everything - not very elegant at all...

Heidi




As you say, you can derive the starting shift number from the
start-time and the end shift with the end-time. The elapsed time is
merely the end-date+time minus the start-date+time, and any integer
part of this represents whole days, so all 3 shifts would have 8hrs
times the number of days. That only leaves the fraction of a day
elapsed time. You can work out how much time remains in the starting
shift by subtracting the start time from the shift's finish time
(taking account of any cross-midnight times), so if you take this away
from the partial-day elapsed time it will leave you with a remainder
which may or may not be greater than 8 hours - if it isn't then the
remaining time gets allocated to the following shift. If it is greater
than 8 hours and less than 16, then 8 hours gets allocated to the
following shift, with any remaining time (up to 8 hours) added to the
final shift. If there is anything left (which must be less than 8
hours) it gets added to the starting shift (wrap-around, but less than
24 hours).

That's the theory, anyway - much simpler than a problem I've been
working on to allocate long-duration telephone calls to the appropriate
charging period of varying durations! It's a bit late now here in the
UK, so I'll continue with this tomorrow - see if I can translate it
into Excel formulae.

Hope this helps.

Pete