Overtime Calculation with Multiple Work Rates

Question

I need a formula that will accomplish the following, and I am not sure how to create it.

We have employees who have 2 different rates, one for Work and one for Drive. Our payroll spreadsheet has each day of the week broken into work and drive hours. We need a formula that will take the summed total for work and drive hours, apply the first 40 total hours to their respective column, and add the remaining work and drive hours to the work and drive overtime columns. I have figured out how to create a formula that will put the first 40 hours into Work and the remaining hours into Work-OT, but cannot determine how to accomplish this considering both rates, ie. if the employee hits 40 hours half way through their drive on Friday, the remaining drive hours as well as drive hours from Sat + Sun are put to the Drive-OT column, and the remaining work hours for Sat + Sun are put to Work-OT.

philip west · Answer

I think i've done it.I'm attaching a workbook that follows the screenshot, so i'm hoping you can copy/paste your data and it will just work for you.&nbsp;To explain though. It has 3 worksheets. The first (Example) is basically the sheet I used to try and work it out. I think it might be easier to understand what is happening on that sheet, but it's not usable.&nbsp;The one called Data, is where you should update your records, and the last one (helpers) is where the calculations are happening. You need to make sure that there is a row of 'helpers' for every row in data.&nbsp;Do test it, I've been looking at it long enough that i'm not 100% i can see it properly now :D

ryder.simmonscovering · Answer

I applied the changed code to my google sheet document and with a few adjustments it seems to be working perfectly!&nbsp; Thank you so much, I really appreciate your help, this functionality will streamline our payroll process significantly.&nbsp; Much thanks again!

philip west · Answer

Do you have an example of how your data is laid out?

ryder.simmonscovering · Answer

&nbsp;This is a sample of how the sheet is set up.&nbsp; Ideally it would work so that Work (W) and Drive (D) hours columns are added to their respective columns on the right, until W+D combine for 40 hours, and then each hour after that would go into W/OT if W hours and D/OT if D hours.&nbsp; I can provide cell numbers if needed.

ryder.simmonscovering · Answer

I can't express how helpful this was!&nbsp; Thank you so much.&nbsp;&nbsp;I see that the sheet is working properly with the numbers you had input, but when I try to insert an employee's actual hours for the week, the hours broken into W/D/OT don't agree with what I get when I do it manually, so I am not sure if I am inputting incorrectly or if I need to change something about the sheet. For example:&nbsp;The hours you input total to 46W 14D.&nbsp; The sheet breaks them correctly into 31/9/15/5, but the last line (A12:AB12) shows the breakdown as 31/8/15/5.&nbsp;When I put in the hours Ive attached in the screenshot, the totals are 47W 17.5D which I manually translated into 28/12/19/5.5, but the worksheet shows it as 28/9/19/5.5.&nbsp;I have tried to diagnose why it seems to be miscalculating the regular Drive time, as the W+D must add to 40.&nbsp; The only thing that I found, was that even if I delete all of the data on line 3, it still puts one hour to Drive time even though no hours are filled in at all.&nbsp; Not sure if this is the source of the issue.&nbsp;Please let me know if you have any insights.&nbsp; Thanks so much for everything youve done already.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

Forum Discussion

Overtime Calculation with Multiple Work Rates

9 Replies

Resources