Solved

Excel Calculate overtime from existing spreadsheet.

Posted on 2016-11-17
13
30 Views
Last Modified: 2016-11-20
I have a report from a payroll system that a customer uses that calculates overtime hours incorrectly.  For example an employee may show 48 hours regular time in one pay period and 4 hours over time.  I need to have the regular time hours caped at 40 and the extra hours moved to the same employee on their over time line.  I'm attaching an example to make things a little easier to understand.  There are also cases where and employee will show 48 hours regular time and no line for overtime.  In those cases I need to add a line for overtime for that employee.
0
Comment
Question by:Mwvarner
  • 7
  • 4
  • 2
13 Comments
 
LVL 23

Expert Comment

by:Brian B
ID: 41892047
I don't see an attachment, and maybe this would help answer the question, but do you to actually change the value in the overtime column? That would require a macro. If you are okay with adding a column, it could be done with a calculation in column C, "adjusted overtime":

=if(A1>40,A1-40+B1,B1)
A1 is regular hours claimed
B1 is overtime claimed
0
 

Author Comment

by:Mwvarner
ID: 41892058
I thought the file attached.  I'll try again.
Payroll-Report.xlsx
0
 
LVL 23

Expert Comment

by:Brian B
ID: 41892065
Oh I see. Regular pay and overtime are separate entries. That might take a bit more work. Another expert may get an answer before I do, but that's okay.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 81

Expert Comment

by:byundt
ID: 41892438
Here are two formulas for regular time and overtime. I put them in two new columns, I & J in your sample workbook. These formulas return an amount for Regular and Overtime on the first line where an Employee ID is listed. On other rows for that Employee ID, an empty string (looks like a blank) is returned.
=IF(MATCH($C2,$C$2:$C$10000,0)=(ROW($C2)-ROW($C$2)+1),MIN(40,SUM(SUMIFS($F:$F,$C:$C,$C2,$E:$E,{"REGLAR","OVTIME"}))),"")      
=IF(MATCH($C2,$C$2:$C$10000,0)=(ROW($C2)-ROW($C$2)+1),MAX(0,SUM(SUMIFS($F:$F,$C:$C,$C2,$E:$E,{"REGLAR","OVTIME"}))-40),"")

As written, these formulas only include hours with Pay Code marked as REGLAR or OVTIME. If you had a category for paid time off, that should be added to the array constant {"REGLAR","OVTIME"}. By matching the Pay Code to items in the array constant, you exclude time marked as ABSENT.
-Payroll-ReportQ28983832.xlsx
1
 

Author Comment

by:Mwvarner
ID: 41892909
That solution is perfect for people that only work in one department.  But I have people who will work in 3 or 4 departments in a pay period.  Can the match function match on 2 fields like Employee ID and Department?
0
 

Author Comment

by:Mwvarner
ID: 41893089
Actually I thing I've figured out a solution to the issue.  I'm creating a row with Employee ID and Department concatenated for each record and I'll match on that.

Thanks for the help.
0
 

Author Comment

by:Mwvarner
ID: 41893093
Thanks again for the assistance.  Your formulas matching on a concatenated field with Employee ID and Department works great.
0
 
LVL 81

Expert Comment

by:byundt
ID: 41893101
I don't think concatenating employee ID and department is going to work for you. The government will likely argue that over 40 hours means overtime no matter how many departments an employee works in.

I can extend the formula to match both department and employee ID--but you will need to define the logic. If there is overtime, which department pays it?

For example, suppose employee 123456 normally works 19 hours for department A and 19 for department B. But this week, he worked 19 hours for department A and 25 for department B. Do the departments then each pay for 2 hours of overtime? Or does department B pay for 4 hours of overtime?
0
 

Author Comment

by:Mwvarner
ID: 41893218
According to my payroll people, if an employee has 40 hours or more in any department(s) then every hour worked in another department is automatically overtime.  In your example department B would be charged the overtime.
0
 
LVL 81

Expert Comment

by:byundt
ID: 41893264
In my example, suppose the employee worked in department B early in the week, then in department A the rest of the week. Your payroll department would then say that department A was responsible for all the overtime--but there is insufficient information to determine this fact on the workbook you posted.
0
 

Author Comment

by:Mwvarner
ID: 41893315
I know.  I don't think which department pays the overtime is a big consideration for this customer.  Just as long as the associate is paid properly.
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 41893493
I added a concatenation column I as you had previously mentioned. This moved the Regular and Overtime hours columns to J & K, using formulas:
=IF(MATCH($I2,$I$2:$I$10000,0)=(ROW($I2)-ROW($I$2)+1),MIN(40,SUM(SUMIFS($F:$F,$I:$I,$I2,$E:$E,{"REGLAR","OVTIME"})),MAX(0,40-SUM(SUMIFS($F$1:$F1,$C$1:$C1,$C2,$E$1:$E1,{"REGLAR","OVTIME"})))),"")
=IF(MATCH($I2,$I$2:$I$10000,0)=(ROW($I2)-ROW($I$2)+1),MAX(0,SUM(SUMIFS($F:$F,$I:$I,$I2,$E:$E,{"REGLAR","OVTIME"}))+SUM(SUMIFS($F$1:$F1,$C$1:$C1,$C2,$E$1:$E1,{"REGLAR","OVTIME"}))-40-SUM(SUMIFS($K$1:$K1,$C$1:$C1,$C2,$E$1:$E1,{"REGLAR","OVTIME"}))),"")

As written, these formulas assign worked hours to Regular and Overtime based on order of entry. For example, if Jim Jones works in four different departments (19, 25, 3 & 2 hours respectively), the first department likely gets 19 Regular hours, the second gets 21 Regular hours plus 4 Overtime, and the last two get 3 & 2 hours of Overtime.

When formulas get this complex in my own workbooks, I start thinking about using a user-defined function (VBA code) instead of a worksheet formula that nobody else can understand. That said, there is something good about eliminating all VBA from a workbook.

If you identify any more edge cases that need to be handled, please post a workbook with sample data showing the problem. I will most likely respond with a UDF.
-Payroll-ReportQ28983832.xlsx
0
 

Author Closing Comment

by:Mwvarner
ID: 41895148
Perfect solution for my needs.
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question