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