Calculate time worked based on start / end times & working hours

[MeatLightning]

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!

 

[FloMM2]

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.

 

[KIOhio]

Meat,
This isn’t so tough, just tedious. Your opening calculation, which yields
the actual number that Excel uses is a good start. Convert that to minutes
with a Lookup table. One minute equals 0.000694386573741212. Add it to your
previous total to make as long a list of minutes as you need for the table.
The lookup keyed from the duration will return the number of minutes for each
record. Total the minutes for each ID, task or whatever category you want.
You can then convert the minutes to decimal hours with arithmetic division.
The decimal remainder can then be converted to minutes is you like.

KIOhio

 

[MeatLightning]

Thanks FloMM2 & KIOhio! Really appreciate it.

I think you are missing what I need? Or maybe I'm just not seeing it in your
solutions?

I need to subtract any non-working time from the durations of my various
tasks, then add them all up based on their ID.

Basically, the timekeeper is dumb and doesn't account for when people are
actually working on tasks.

So, if a task starts on Saturday and completes on Monday, the timekeeper
doesn't account for the fact that no work was done on Sunday (day off!) and
says the task took 3 days (when it really took 2).

What I can't wrap my head around is - how do you take a start and stop
timestamp and find the duration, then determine what portion of that duration
was not applicable and subtract that out.

Further complicating things (for me at least) is that the working hours are
not uniform. I basically need to look at each record's start & end times,
figure out the day of the week, then based on that determine the non-working
hours, then figure out what, if any, portion of that time was recorded by the
timekeeper and subtract it out.

Clear as mud no?

Anyway, thanks again!
-meat