Meatlightning,
This is what I came up with:

ID Task StartDate StartTime EndDate EndTime Duration Totals
01 sift 6/24/09 22:30 6/25/09 2:30 0.1667
01 stack 6/25/09 2:31 6/25/09 8:00 0.2285
01 load 6/25/09 8:01 6/25/09 15:00 0.2910 16.47
02 sift 6/25/09 15:01 6/26/09 1:00 0.4160
02 stack 6/25/09 21:01 6/26/09 1:00 0.1660
02 load 6/26/09 1:01 6/26/09 8:00 0.2910 20.95

Starting in cell A1 with "ID",cell B1-"Task" and so on.
Column "C2 thru C7" format as Date, same "E2 thru E7".
Column "D2 thru D7" format as time (13:30), same with "F2 thru F7".
Column "G2 thru G7" format as number, I used 4 decimal places.
Add column heading "Totals"
In Cell H4 put formula "=(SUMIF(A2:A4,"+01",G2:G4)*24)" without "".
In Cell H7 put formula "=(SUMIF(A5:A7,"=02",G5:G7)*24)" without "".

Hope this helps, or gets you headed in the right direction.






"MeatLightning" wrote:

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!