Ok, this one is a biggie... if you call pull this one off, I'll hail you as
the king of excel and sing your praises far and wide... no sweat if there are
no takers... just figured I'd ask!

I have data that looks like this:

ID | Task | Start | End | Duration
-------------------------------------------------------------
01 | sift | 6/24/09 10:30 PM | 6/25/09 2:30 AM | 0.166666667
01 | stack | 6/25/09 2:31 AM | 6/25/09 8:00 AM | 0.228472222
01 | load | 6/25/09 8:01 AM | 6/25/09 3:00 PM | 0.290972222
02 | sift | 6/25/09 3:01 PM | 6/25/09 9:00 PM | 0.249305556
02 | stack | 6/25/09 9:01 PM | 6/26/09 1:00 AM | 0.165972222
02 | load | 6/26/09 1:01 AM | 6/26/09 8:00 AM | 0.290972222


I need to figure out how much total time was spent on a given ID (or job)
considering the actual working hours.

Of course, working hours are a bit weird in that they cross days -
specifically:
Mon - 6AM to 11:59PM [19hrs]
Tue - 12AM to 4AM, 6AM to 11:59PM [22hrs]
Wed - 12AM to 4AM, 6AM to 11:59PM [22hrs]
Thu - 12AM to 4AM, 6AM to 11:59PM [22hrs]
Fri - 12AM to 4AM, 6AM to 6PM [15hrs]
Sat - 6AM to 6PM [12hrs]
Sun - [0hrs]

Crazy right?

Anyway, using the info above, I'd expect the calculation to show that ID
"01" took a total of 14hrs, 28mins.

This is because the raw sum of the durations comes to 16hrs, 28mins but
includes 2 hours of non-working time.

Make sense?

Thanks in advance!
-meat

p.s. love this forum!