Excel/OT AND Double time calculations
QUESTION: Hello! I'm using Excel 2010. I need to enter a formula that will calculate OT(overtime) for daily hours worked over 8 but I need to pay Double time for daily hours worked over 12. How do I allow for that? I'm thinking one row for OT and one row for double time. I've entered =IF(SUM(G1:G2)>8,SUM(G1:G2)-8,0)to calculate the OT on the 15 regular hours (below sum of G1 and G2)but I'm not going to pay "7" at the OT rate. I'm going to pay only 4 at the OT rate, the other 3 hours must go at the double time rate. The formula in the "DOT"(double time) row is currently =IF(SUM(G3)>4,SUM(G3)-4,0) but that doesn't work if I reduce the OT row to accurately reflect "4".
Daily total hours will be entered in the "REG" rows (some people may have only one REG row, those with different depts. will have 2 or more) and the OT row should only show hours totaling over 8 through 12. Anything over 12 should calculate in the "DOT" row. Please and THANK YOU!!
Those worked for me. You can adjust the reference to Sum(G1:G2) to reflect how the REG hours are to be entered
---------- FOLLOW-UP ----------
QUESTION: Thank you SO much for your quick response Tom! Your formulas work perfectly. But as I go along, I need more help. I've entered your formulas in the OT and DOT rows but I'd like the row named "TRUE REG" (for now) to display the total regular hours. For example, on the 11 hour workday, it should calculate "8".
Also, I'll need to change my formulas in the week one and week two columns to calculate any "weekly OT". These calculations are for our new California branch. CA labor laws state:
OT for anything over 8 hrs in one day
OT for anything over 40 hrs of reg time for that work week
(no "pyramiding of time, so once an hour is paid as OT either daily or weekly it cannot be counted again for OT)
Double time for daily hours over 12
Double time on the 7th consecutive day of work for any time over 8 hours worked.
I've entered a seemingly crazy scenario I found. Total of 81 hours in one week. If we follow the rules, the spreadsheet should calculate: 40 hrs of reg time, 32 OT and 9 hrs of Double time. Can you help with this? It is GREATLY appreciated!
You picture is too small to make out any details. Why not send me a sample spread sheet and mark where you want the formula and how they should act. You might put in the correct and answer on the sheet as well. If I have questions, then I can ask them of you.