Forum Discussion
Overtime Calculation with Multiple Work Rates
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.
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
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.
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.
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.
