I have a time sheet report that breaks down the employee ID, pay rate, date
in, time in, date out, time out, and total hours worked. I need to calculate
weekly (not daily) overtime per employee (i.e. at each change in employee ID)
based on a bi-weekly pay frequency. I am struggling with how to accomplish
this within an Excel function. Is there a way to sum the total hours for week
1 (04/11 - 04/17) and seperatly for week 2 (04/18 - 04/24), per employee, at
each change in the employee ID? I don't want to use the subtotal function in
Excel since I will be using this data to create an import into our payroll
system and it needs to be in a specific file layout. Once I have the weekly
hours summed, I know how to extract the overtime hours but I need assistance
in getting the weekly hours calculated per employee. This information will
change each pay period that I run this report. I have provided a sample of
the layout of my current file below.

A B C D E F G H
EmpID JobID DateIn TimeIn DateOut TimeOut Rate TotHr
101 25 4152010 15:35 4162010 5:00 0.00 13.42
101 25 4162010 18:44 4162010 18:45 0.00 0.02
102 25 4152010 17:48 4162010 5:00 0.00 11.2
102 25 4162010 18:49 4172010 2:20 0.00 7.52
102 25 4172010 10:21 4182010 5:00 0.00 18.65
102 25 4202010 9:11 4212010 5:00 0.00 19.82
102 25 4212010 10:57 4222010 5:00 0.00 18.05
315 25 4152010 17:12 4152010 23:32 5.00 6.33
315 25 4162010 17:28 4162010 23:43 5.00 6.25


Any assistance you can provide is greatly appreciated!


--
J Harris