Overtime Calculation with Multiple Work Rates

[ryder.simmonscovering]

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. 

[#2]

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

[#3]

This is a sample of how the sheet is set up.  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.  I can provide cell numbers if needed.

[#4]

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.

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.

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.

Do test it, I've been looking at it long enough that i'm not 100% i can see it properly now :D

[#5]

I can't express how helpful this was!  Thank you so much.  

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:

The hours you input total to 46W 14D.  The sheet breaks them correctly into 31/9/15/5, but the last line (A12:AB12) shows the breakdown as 31/8/15/5.

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.

I have tried to diagnose why it seems to be miscalculating the regular Drive time, as the W+D must add to 40.  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.  Not sure if this is the source of the issue.

Please let me know if you have any insights.  Thanks so much for everything youve done already.

[#6]

I think i've spotted the mistake. Have a look at the attached. The only change i've made is removing what seems to be an unnecessarily complicated calculation in the first column of the total time section.

It's now matching what you manually calculated.

[#7]

Hello,

That fixed everything.  It seems to be working perfectly thank you so much.  

I have tried to upload it to Google Sheets but when I do so it doesn't calculate correctly.  I was thinking it has something to do with the code in the Helpers sheet.  I am not sure if there is a difference in functions between Excel and Google Sheets, but it seems that the only ones used are IF and SUM which I know work the same in both.  Perhaps it is something more simple, like a difference in how the code is laid out (does "" need to be written differently in sheets?) but I am not sure.  If sheets is not something you have experience with I can look elsewhere, you have been so generous already.

[#8]

well now. I just tried uploading it to docs and your right its all gone wrong.

Looking at it, it has to do with how excel and docs seem to handle empty cells. in the overtime calculation excel is putting a 0 in cells and docs is not. That means that other parts of the formula that were checking if the cell is empty or not weren't working.

I've added a bit of a fix.. i dont really use docs so there might be a 'proper' way to adapt the formula, but i've added a max(0,A3) to my formula which kinda mimics excel and fixes it (i think).

I've quite enjoyed this puzzle. (assuming this fixes your problem :D)

[#9]

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

[#10]

Super. Glad it worked out.