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]

This should produce number of hours:

=IF(C2>B2,(C2-B2)*24,(C2-B2)*24+24)

 

[Safety_3rd]

Steve, that gives me the total hours worked in the day in decimal form. I then need to determine how to sort that data into the work week and sum based on work day. I also need to figure out how to address the days when an employee clocks in on Saturday and works till Sunday morning. The time Saturday goes on one week and the Sunday goes on the next week. Thank you

 

[steve the fish]

Not sure its possible with the data you have there. At least I cant think of a way. How do we decide where one week ends and the next starts? What if an employee has a week off for instance? Can you bring dates in rather than day names?

 

[Safety_3rd]

Punch Date Day of Week Punch In 1 Punch Out 1 Hours 1
03/01/2014 Saturday 21:00 02:14 5:14
03/02/2014 Sunday 02:55 07:40 4:45
03/03/2014 Monday 02:20 07:36 5:16
03/04/2014 Tuesday 02:35 04:05 1:30
03/05/2014 Wednesday 21:37 00:50 3:13
03/06/2014 Thursday 01:31 06:26 4:55
03/07/2014 Friday 01:34 06:26 4:52
03/08/2014 Saturday 02:08 06:39 4:31
03/09/2014 Sunday 01:48 07:35 5:47
03/10/2014 Monday 01:53 06:46 4:53
03/11/2014 Tuesday 02:30 06:34 4:04
03/12/2014 Wednesday 02:38 06:44 4:06
03/13/2014 Thursday 01:36 06:21 4:45
03/14/2014 Friday 01:26 01:46 0:20
03/15/2014 Saturday 21:00 00:54 3:54
03/16/2014 Sunday 01:21 06:02 4:41
03/17/2014 Monday 01:45 07:04 5:19
03/18/2014 Tuesday 02:20 07:03 4:43
03/19/2014 Wednesday 02:32 06:00 3:28
03/20/2014 Thursday 01:48 06:34 4:46
03/21/2014 Friday 01:48 07:19 5:31
03/22/2014 Saturday 01:06 04:05 2:59
03/23/2014 Sunday 21:00 01:14 4:14
03/24/2014 Monday 02:10 05:30 3:20
03/25/2014 Tuesday 21:00 01:38 4:38
03/26/2014 Wednesday 02:31 07:11 4:40
03/27/2014 Thursday 01:30 07:18 5:48
03/28/2014 Friday 02:11 07:23 5:12

 

[Safety_3rd]

Sorry for the horrible table. I guess if there was a way to sum any hours that fell within a 7 day date range? Is that possible?

 

[steve the fish]

Good. Could you now post that in columns so i can paste into excel?

 

[steve the fish]

Monday 00:00 to sunday 23:59 yeah?

 

[Safety_3rd]

Let me figure out how to post in columns real quick. And the work week is Sunday 00:00 to Saturday 23:59

 

[Safety_3rd]

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][TABLE="width: 454"]
<colgroup><col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"> <col width="146" style="width: 110pt; mso-width-source: userset; mso-width-alt: 5339;"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;" span="2"> <col width="132" style="width: 99pt; mso-width-source: userset; mso-width-alt: 4827;"> <tbody>[TR]
[TD="class: xl64, width: 118, bgcolor: gray"]Punch Date[/TD]
[TD="class: xl64, width: 146, bgcolor: gray"]Day of Week[/TD]
[TD="class: xl64, width: 104, bgcolor: gray"]Punch In 1[/TD]
[TD="class: xl64, width: 104, bgcolor: gray"]Punch Out 1[/TD]
[TD="class: xl64, width: 132, bgcolor: gray"]Hours 1[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]03/01/2014[/TD]
[TD="class: xl63, bgcolor: transparent"]Saturday[/TD]
[TD="class: xl63, bgcolor: transparent"]21:00[/TD]
[TD="class: xl63, bgcolor: transparent"]01:00[/TD]
[TD="class: xl63, bgcolor: transparent"]4:00[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]03/02/2014[/TD]
[TD="class: xl63, bgcolor: transparent"]Sunday[/TD]
[TD="class: xl63, bgcolor: transparent"]01:30[/TD]
[TD="class: xl63, bgcolor: transparent"]07:19[/TD]
[TD="class: xl63, bgcolor: transparent"]5:49[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]03/03/2014[/TD]
[TD="class: xl63, bgcolor: transparent"]Monday[/TD]
[TD="class: xl63, bgcolor: transparent"]01:14[/TD]
[TD="class: xl63, bgcolor: transparent"]07:30[/TD]
[TD="class: xl63, bgcolor: transparent"]6:16[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]03/04/2014[/TD]
[TD="class: xl63, bgcolor: transparent"]Tuesday[/TD]
[TD="class: xl63, bgcolor: transparent"]02:30[/TD]
[TD="class: xl63, bgcolor: transparent"]03:30[/TD]
[TD="class: xl63, bgcolor: transparent"]1:00[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]03/05/2014[/TD]
[TD="class: xl63, bgcolor: transparent"]Wednesday[/TD]
[TD="class: xl63, bgcolor: transparent"]01:36[/TD]
[TD="class: xl63, bgcolor: transparent"]05:14[/TD]
[TD="class: xl63, bgcolor: transparent"]3:38[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]03/06/2014[/TD]
[TD="class: xl63, bgcolor: transparent"]Thursday[/TD]
[TD="class: xl63, bgcolor: transparent"]20:56[/TD]
[TD="class: xl63, bgcolor: transparent"]01:00[/TD]
[TD="class: xl63, bgcolor: transparent"]4:04[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]03/07/2014[/TD]
[TD="class: xl63, bgcolor: transparent"]Friday[/TD]
[TD="class: xl63, bgcolor: transparent"]01:21[/TD]
[TD="class: xl63, bgcolor: transparent"]06:14[/TD]
[TD="class: xl63, bgcolor: transparent"]4:53[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]03/08/2014[/TD]
[TD="class: xl63, bgcolor: transparent"]Saturday[/TD]
[TD="class: xl63, bgcolor: transparent"]04:15[/TD]
[TD="class: xl63, bgcolor: transparent"]06:19[/TD]
[TD="class: xl63, bgcolor: transparent"]2:04[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]03/09/2014[/TD]
[TD="class: xl63, bgcolor: transparent"]Sunday[/TD]
[TD="class: xl63, bgcolor: transparent"]01:30[/TD]
[TD="class: xl63, bgcolor: transparent"]07:20[/TD]
[TD="class: xl63, bgcolor: transparent"]5:50[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]03/10/2014[/TD]
[TD="class: xl63, bgcolor: transparent"]Monday[/TD]
[TD="class: xl63, bgcolor: transparent"]01:33[/TD]
[TD="class: xl63, bgcolor: transparent"]06:12[/TD]
[TD="class: xl63, bgcolor: transparent"]4:39[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]03/11/2014[/TD]
[TD="class: xl63, bgcolor: transparent"]Tuesday[/TD]
[TD="class: xl63, bgcolor: transparent"]01:46[/TD]
[TD="class: xl63, bgcolor: transparent"]06:01[/TD]
[TD="class: xl63, bgcolor: transparent"]4:15[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]03/12/2014[/TD]
[TD="class: xl63, bgcolor: transparent"]Wednesday[/TD]
[TD="class: xl63, bgcolor: transparent"]21:17[/TD]
[TD="class: xl63, bgcolor: transparent"]01:05[/TD]
[TD="class: xl63, bgcolor: transparent"]3:48[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]03/13/2014[/TD]
[TD="class: xl63, bgcolor: transparent"]Thursday[/TD]
[TD="class: xl63, bgcolor: transparent"]01:36[/TD]
[TD="class: xl63, bgcolor: transparent"]06:16[/TD]
[TD="class: xl63, bgcolor: transparent"]4:40[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]03/14/2014[/TD]
[TD="class: xl63, bgcolor: transparent"]Friday[/TD]
[TD="class: xl63, bgcolor: transparent"]01:00[/TD]
[TD="class: xl63, bgcolor: transparent"]04:46[/TD]
[TD="class: xl63, bgcolor: transparent"]3:46[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]