Formula to sum based on a change in another column
 

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

Formula to sum based on a change in another column
 

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.
--
Best Regards,

Luke M
"J Harris" wrote in message
...
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

Formula to sum based on a change in another column
 

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

Hi Harris,

I tried with some template for you. please refer to attachment.

All you have to do is enter the week range in green color and yellow colored region formulae helpful to analyse the data.

You can form a pivot table based on weeknumber in page level

thanks
Bala