A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Formula to sum based on a change in another column



 
 
Thread Tools Display Modes
  #1  
Old May 3rd, 2010, 08:48 PM posted to microsoft.public.excel.worksheet.functions
J Harris
external usenet poster
 
Posts: 4
Default 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  
Old May 3rd, 2010, 09:16 PM posted to microsoft.public.excel.worksheet.functions
Luke M[_4_]
external usenet poster
 
Posts: 451
Default 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  
Old May 4th, 2010, 05:09 AM posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg
external usenet poster
 
Posts: 1,113
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:42 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.