How to automate shift pay/time for split shifts with given constraints.

Is there a way to set up the following for split shifts.

Staff get the following;

10 minutes for Report Time
5 minutes for Clear time

If the break between both shifts is less than 2 hours then it is paid at straight time.

The maximum day span allowed is 14 hours.

Due to these rules, for determining pay, the min and max would be these.

STR 7.25
OT 0

STR 8
OT 6

Though 99% there will never be a job that pays 6 hours overtime. This is just an example for the forum.

Below is one of the rules, word for word. My best interruption of this is the yellow highlighted portions in the workbook, though I could be wrong.

All employees shall be paid for overtime at the rate of one and one‑half times the hourly rate for all work performed after the completion of eight (8) hours work or ten (10) hour day span daily. Jobs having a day span in excess of eleven hours (11) minimum pay at the end of eleven (11) hours shall be eight (8) times the hourly rate and work performed thereafter shall be paid in addition to the minimum guarantee of eight (8) hours.

From management: Employees start to receive 1.5 pay at 10 hours of a day span. They also are guaranteed 8 ST hours at 11 hours span if that many ST hours have not yet been accrued. In this second scenario, the employee would then get OT for the remainder of their work.

If you can help generate all of this, it would be greatly appreciated.

"Limit" may be a poor word choice. Its just the time at the 10th hour of the job.

I'm sorry, I'm not sure what else to say. Its just the time after the start of the job. So, if a job Reports at 5:30AM, then 10 hours later would be 3:30PM and likewise for 11 hours (4:30PM).

Which is referencing the rule from the original post.

"Below is one of the rules, word for word. My best interruption of this is the yellow highlighted portions in the workbook, though I could be wrong.

All employees shall be paid for overtime at the rate of one and one‑half times the hourly rate for all work performed after the completion of eight (8) hours work or ten (10) hour day span daily. Jobs having a day span in excess of eleven hours (11) minimum pay at the end of eleven (11) hours shall be eight (8) times the hourly rate and work performed thereafter shall be paid in addition to the minimum guarantee of eight (8) hours.

From management: Employees start to receive 1.5 pay at 10 hours of a day span. They also are guaranteed 8 ST hours at 11 hours span if that many ST hours have not yet been accrued. In this second scenario, the employee would then get OT for the remainder of their work."

Hey no problem at all and thanks. I'm doing the same over here. Appreciate your time!

Hi there, thanks again.

#2 The break would be unpaid in this case, the break is 3h05.

For number 5, I'm probably wrong, but I understood the 8 hour guarantee as in make up time (the time it takes to reach the 8 hours), not a bonus though.

Can you explain how you got 12 hours for number 6?

Ah, I should have clarified. I'm sorry. Breaks are unpaid, unless it is under 2 hours. Being this break is 3h05, this would not be paid.

Thank you for explaining #5 as well. I will note that applies if the Day Span is less than 11 hours, in this case the job span is 12 hours. Where I was thinking it would take 2h15 at straight time to reach the 8 hours (305p) where OT would kick in OR, the OT wouldn't kick in until the 11th hour mark at 350p resulting in 1 hour of OT and 7.25 Straight being Straight/Overtimes are rounded to quarter hours.

Yes, if you are able.

#2 - Wouldn't I modify the formula to be 0 if FALSE?

#5 - Does my response make sense at all for what I am trying to explain? It appears the formulas is counting the time after the 10 hours AND the 11 hours, but instead of actual time after 11th its an automatic 12 hours. That part confuses me.

Shouldn't the other cells be reduced then after applying this formula?

What should the final result be then? If the Straight time is 7.25, the work time is 8, though the total calculates it as 20 hours, 7.25 Straight/4 for Overtime?

Reviewing managements response, I interpreted it as instead of 8 hours at overtime if worked more than 11, it would be 8 hours at straight because a standard 8 hour workday, if you work less than 8 hours, you are still guaranteed 8 hours of pay regardless. Even though technically your at work for those 12 hours in this case, your not working that entire amount. Again, I could be wrong.