Forum Discussion
Setting up a "smart" count for a column for overtime
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!!!
Here is a solution that calculates overtime using a custom VBA function. Regular time is calculated as total time - overtime.
7 Replies
Hope I was able to help you with this link.
I know I don't know anything (Socrates)
- The vertical columns are all time entered in one 24hour day, and already set up. I actually have a very similar formula for finding those numbers. Thank you for confirming (and simplifying) I had that set up right.
My Issue is totaling the time for a week, once it hits 40 hours (regardless of if its in 3 days or 5) it is overtime, but ALSO after 8 hours in a day. So often, 3-4 days into the week they are hitting all OT, so less than 40 hours of regular time is accrued. Understand?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.
