Formula to sum based on a change in another column

[J Harris]

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!

 

[Luke M]

Would a PivotTable work?

Assuming the DateIn field is truly a date, and not just the number 4122010
(if it is, use this formula:
=DATE(RIGHT,C2,4),LEFT(C2,LEN(C2)-6),MID(C2,LEN(C2-6),2))
to convert)

Once in thre PivotTable, you can right-click the field, and group by days.
If you need to display both the 7-day and 14-day totals at same time, you
could create a second column in your data area that references the first
date column:
=C2
And then in your PivotTable, add that field as well, but set it for 14-day
grouping.

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Compensated for shifts
overlapping week boundaries.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/05_03_10.xlsx
Pdf preview:
http://www.mediafire.com/file/kjiyajwmdaw/05_03_10.pdf