Forum Discussion
Overtime Calculation with Multiple Work Rates
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!
Super. Glad it worked out.