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.

Day of week	Punch in	Punch out	Hours
Saturday	22:00	4:15	6:15
Sunday	04:15	05:30	1:15
Monday	21:00	05:30	8:30
Tuesday	etc...	etc...	etc...
Wednesday
Thursday
Friday
Saturday
Monday
Tuesday
Wed
Thur
Fir
Mon
Tue
Wed
Thur
Fri
Sat

<tbody>
</tbody>

 

[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)

Punch Date	Day of Week	Punch In 1	Punch Out 1	Hours 1	Weeknum	After Sat 00:00		Weeknum	Hours Worked
01/03/2014	Saturday	21:00	01:00	4.00	9	1.00		1	0.00
02/03/2014	Sunday	01:30	07:19	5.82	10	0.00		2	0.00
03/03/2014	Monday	01:14	07:30	6.27	10	0.00		3	0.00
04/03/2014	Tuesday	02:30	03:30	1.00	10	0.00		4	0.00
05/03/2014	Wednesday	01:36	05:14	3.63	10	0.00		5	0.00
06/03/2014	Thursday	20:56	01:00	4.07	10	0.00		6	0.00
07/03/2014	Friday	01:21	06:14	4.88	10	0.00		7	0.00
08/03/2014	Saturday	04:15	06:19	2.07	10	0.00		8	0.00
09/03/2014	Sunday	01:30	07:20	5.83	11	0.00		9	3.00
10/03/2014	Monday	01:33	06:12	4.65	11	0.00		10	28.73
11/03/2014	Tuesday	01:46	06:01	4.25	11	0.00		11	26.97
12/03/2014	Wednesday	21:17	01:05	3.80	11	0.00		12	0.00
13/03/2014	Thursday	01:36	06:16	4.67	11	0.00		13	0.00
14/03/2014	Friday	01:00	04:46	3.77	11	0.00		14	0.00

<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>
</tbody>