If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|