The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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.

=if(A1>40,A1-40+B1,B1)

A1 is regular hours claimed

B1 is overtime claimed

I thought the file attached. I'll try again.

Payroll-Report.xlsx

Payroll-Report.xlsx

=IF(MATCH($C2,$C$2:$C$1000

=IF(MATCH($C2,$C$2:$C$1000

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

Thanks for the help.

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?

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

=IF(MATCH($I2,$I$2:$I$1000

=IF(MATCH($I2,$I$2:$I$1000

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