Mar 22 2022 08:40 AM
I am trying to set up a totaling function for regular and overtime in a column. Here is the catch: Over time is met at 8 hours a day AND whenever the employee hits 40 hours in a week. I can run a formula that captures one or the other, but not both. Example of data:
EE1 EE2 EE3
16. 9.5. 6
18. 9.5. 8.5
17. 10. 9
15. 9.5. 8
14. 6. 9
total:80. 44.5. 40.5
Reg: 22. 33.5. 38
OT: 58. 11. 2.5
Where these are the correct totals. I just cant seem to format any sort of formula that switches process when the running total hits 40 and carries over to OT. Any help is much appreciated!!!
Mar 22 2022 08:54 AM
Hope I was able to help you with this link.
I know I don't know anything (Socrates)
Mar 22 2022 09:18 AM
Mar 22 2022 10:12 AM
@loganak Have a look at the attached file. It contains two possible ways to calculate this. Choose the one that works in your Excel version.
Mar 22 2022 10:44 AM
Mar 22 2022 10:46 AM
Mar 22 2022 12:43 PM
SolutionHere is a solution that calculates overtime using a custom VBA function. Regular time is calculated as total time - overtime.
Mar 22 2022 12:52 PM
Mar 22 2022 12:43 PM
SolutionHere is a solution that calculates overtime using a custom VBA function. Regular time is calculated as total time - overtime.