Calculate total hours in a work week using workdays and punch in and out times

[Safety_3rd]

I have a list days and clock in and out times. I would like to calculate the total number of hours worked in a work week. The work week begins midnight Sunday and ends at 11:59:59 Saturday. The punch in and out times are in military format. Employees don't work every day of the week. My problems are that if an employee clocks in Saturday at 22:00 and out at 04:15 on Sunday morning 2 hours will go on the previous week and 4:15 hours in the start of the next week. The total hours need to be in decimal form not hh:mm:ss, and I cant just auto sum every 7 lines because an employee might only work 4 days in one week but all 7 in the next week. Any help would be great! Thank you. [TABLE="width: 500"]
<tbody>[TR]
[TD]Day of week
[/TD]
[TD]Punch in
[/TD]
[TD]Punch out
[/TD]
[TD]Hours
[/TD]
[/TR]
[TR]
[TD]Saturday
[/TD]
[TD]22:00
[/TD]
[TD]4:15
[/TD]
[TD]6:15
[/TD]
[/TR]
[TR]
[TD]Sunday
[/TD]
[TD]04:15
[/TD]
[TD]05:30
[/TD]
[TD]1:15
[/TD]
[/TR]
[TR]
[TD]Monday
[/TD]
[TD]21:00
[/TD]
[TD]05:30
[/TD]
[TD]8:30
[/TD]
[/TR]
[TR]
[TD]Tuesday
[/TD]
[TD]etc...
[/TD]
[TD]etc...
[/TD]
[TD]etc...
[/TD]
[/TR]
[TR]
[TD]Wednesday
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thursday
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Friday
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Saturday
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monday
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wed
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thur
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fir
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mon
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tue
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wed
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thur
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fri
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sat
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

I can do it with helper columns. No doubt a genius could do it better but far as i can tell it works.

In E2: =IF(D2>C2,(D2-C2)*24,(D2-C2)*24+24)
In F2: =WEEKNUM(A2)
In G2: =IF(AND(B2="Saturday",C2>D2),D2*24,0)

In I2:I15: 1,2,3,4,etc
In J2: =SUMPRODUCT(--($F$2:$F$15=I2),$E$2:$E$15)-IFERROR(VLOOKUP(I2,$F$2:$G$15,2,0),0)+IFERROR(VLOOKUP(I2-1,$F$2:$G$15,2,0),0)

[TABLE="class: grid, width: 500, align: center"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:3108;width:64pt" width="85"> <col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:2157;width:44pt" width="59"> <col style="mso-width-source:userset;mso-width-alt:2596;width:53pt" width="71"> <col style="mso-width-source:userset;mso-width-alt:3547;width:73pt" width="97"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2596;width:53pt" width="71"> <col style="mso-width-source:userset;mso-width-alt:3510;width:72pt" width="96"> </colgroup><tbody>[TR]
[TD="width: 77, align: center"]Punch Date
[/TD]
[TD="width: 85, align: center"]Day of Week[/TD]
[TD="width: 70, align: center"]Punch In 1[/TD]
[TD="width: 81, align: center"]Punch Out 1[/TD]
[TD="width: 59, align: center"]Hours 1[/TD]
[TD="width: 71, align: center"]Weeknum[/TD]
[TD="width: 97, align: center"]After Sat 00:00[/TD]
[TD="width: 64, align: center"][/TD]
[TD="width: 71, align: center"]Weeknum[/TD]
[TD="width: 96, align: center"]Hours Worked
[/TD]
[/TR]
[TR]
[TD="align: center"]01/03/2014[/TD]
[TD="bgcolor: transparent, align: center"]Saturday[/TD]
[TD="bgcolor: transparent, align: center"]21:00[/TD]
[TD="bgcolor: transparent, align: center"]01:00[/TD]
[TD="align: center"]4.00[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]1.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"]02/03/2014[/TD]
[TD="bgcolor: transparent, align: center"]Sunday[/TD]
[TD="bgcolor: transparent, align: center"]01:30[/TD]
[TD="bgcolor: transparent, align: center"]07:19[/TD]
[TD="bgcolor: transparent, align: center"]5.82[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"]03/03/2014[/TD]
[TD="bgcolor: transparent, align: center"]Monday[/TD]
[TD="bgcolor: transparent, align: center"]01:14[/TD]
[TD="bgcolor: transparent, align: center"]07:30[/TD]
[TD="bgcolor: transparent, align: center"]6.27[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"]04/03/2014[/TD]
[TD="bgcolor: transparent, align: center"]Tuesday[/TD]
[TD="bgcolor: transparent, align: center"]02:30[/TD]
[TD="bgcolor: transparent, align: center"]03:30[/TD]
[TD="bgcolor: transparent, align: center"]1.00[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"]05/03/2014[/TD]
[TD="bgcolor: transparent, align: center"]Wednesday[/TD]
[TD="bgcolor: transparent, align: center"]01:36[/TD]
[TD="bgcolor: transparent, align: center"]05:14[/TD]
[TD="bgcolor: transparent, align: center"]3.63[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"]06/03/2014[/TD]
[TD="bgcolor: transparent, align: center"]Thursday[/TD]
[TD="bgcolor: transparent, align: center"]20:56[/TD]
[TD="bgcolor: transparent, align: center"]01:00[/TD]
[TD="bgcolor: transparent, align: center"]4.07[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"]07/03/2014[/TD]
[TD="bgcolor: transparent, align: center"]Friday[/TD]
[TD="bgcolor: transparent, align: center"]01:21[/TD]
[TD="bgcolor: transparent, align: center"]06:14[/TD]
[TD="bgcolor: transparent, align: center"]4.88[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"]08/03/2014[/TD]
[TD="bgcolor: transparent, align: center"]Saturday[/TD]
[TD="bgcolor: transparent, align: center"]04:15[/TD]
[TD="bgcolor: transparent, align: center"]06:19[/TD]
[TD="bgcolor: transparent, align: center"]2.07[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"]09/03/2014[/TD]
[TD="bgcolor: transparent, align: center"]Sunday[/TD]
[TD="bgcolor: transparent, align: center"]01:30[/TD]
[TD="bgcolor: transparent, align: center"]07:20[/TD]
[TD="bgcolor: transparent, align: center"]5.83[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]3.00[/TD]
[/TR]
[TR]
[TD="align: center"]10/03/2014[/TD]
[TD="bgcolor: transparent, align: center"]Monday[/TD]
[TD="bgcolor: transparent, align: center"]01:33[/TD]
[TD="bgcolor: transparent, align: center"]06:12[/TD]
[TD="bgcolor: transparent, align: center"]4.65[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]28.73[/TD]
[/TR]
[TR]
[TD="align: center"]11/03/2014[/TD]
[TD="bgcolor: transparent, align: center"]Tuesday[/TD]
[TD="bgcolor: transparent, align: center"]01:46[/TD]
[TD="bgcolor: transparent, align: center"]06:01[/TD]
[TD="bgcolor: transparent, align: center"]4.25[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]26.97[/TD]
[/TR]
[TR]
[TD="align: center"]12/03/2014[/TD]
[TD="bgcolor: transparent, align: center"]Wednesday[/TD]
[TD="bgcolor: transparent, align: center"]21:17[/TD]
[TD="bgcolor: transparent, align: center"]01:05[/TD]
[TD="bgcolor: transparent, align: center"]3.80[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"]13/03/2014[/TD]
[TD="bgcolor: transparent, align: center"]Thursday[/TD]
[TD="bgcolor: transparent, align: center"]01:36[/TD]
[TD="bgcolor: transparent, align: center"]06:16[/TD]
[TD="bgcolor: transparent, align: center"]4.67[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
[TR]
[TD="align: center"]14/03/2014[/TD]
[TD="bgcolor: transparent, align: center"]Friday[/TD]
[TD="bgcolor: transparent, align: center"]01:00[/TD]
[TD="bgcolor: transparent, align: center"]04:46[/TD]
[TD="bgcolor: transparent, align: center"]3.77[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]0.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]0.00[/TD]
[/TR]
</tbody>[/TABLE]