Building a payroll system and need formula help with splitting up total hours between Daily Hrs, time and a half pay, and doubletime. Here’s my setup:
My “Total Hrs/day” is the actual total of all hours worked, calculated with:
Then, I want to limit the amount of hours from the “Total Hrs/Day” that can go into “Daily Hrs” to 8 hours of regular pay. I do this with:
IF((OUT-IN)-BREAK<=8, (OUT-IN)-BREAK, 8)
This means that if 10 hours were worked, “Daily Hrs” is capped at 8.
I want the remaining two hours to populate into the “time and a half” to be multiplied by that 1.5x rate. The caveat is that I need this to be capped at 4 hours of overtime, before spilling any remaining hours from the “Total Hrs/day” into “doubletime”.
I can then spill the remaining hours into the “time and a half” with this formula above, but I can’t figure out how to also cap it at 4 hours before sending the remainder to doubletime. I tried this but am having some issues.
(OUT-IN)-BREAK ≤ 8,
(OUT-IN)-BREAK ≥ 12,
Thanks in advance for your help!