Calculating overtime and double time using carryover hours from previous shift

[jehster1]

Hi all!

I run payroll for concerts using a workbook I made a few years ago (with some help from you guys back then!) It's not uncommon for people to work 18-26 hours split over 3 shifts (Load In, Show Call, Load Out). I had it all running smoothly but now the contract includes Double Time pay. I have the LI Check In sheet working properly but can't nail down the correct formula for the SC Check In sheet or the LO Check In sheet.

For the SC and LO Check In sheets I have a column that shows carry-over hours (from previous shifts), the formula should check that column and the Hours column (time worked on current shift) and do the following ruleset:
If Hours + Carry-over are less than 8 then put Hours in Straight.
If Hours + Carry-over are between 8 and 14, put Hours before 8 in Straight until 8 is reached, then remaining hours in OT up to 14.
If remaining hours go past 14, put Hours between 8 and 14 in OT until 14 is reached, then remaining hours in DT.

I've played around with formulas and helper columns and has various parts working but couldn't get it to work as a whole.

Thanks in advance for any help and/or ideas!

*Clean workbook attached

[JeteMc]

On the SC Check In sheet the following formulas produce the expected output.
1. For OT: =IFERROR(ROUND(IF(SUM([@[C/O]],[@Hours])>14,14-[@[C/O]],IF(SUM([@[C/O]],[@Hours])>8,SUM([@[C/O]],[@Hours])-8,[@Hours]-[@Straight])),2),"")
2. For DT: =[@Hours]-SUM([@Straight],[@OT])
On the LO Check In sheet only five expected outputs are given although there are six so I assume the five shown exclude Straight, OT and DT for John Smith 6
1. For Straight: =IF([@[LO OUT]]="","",IF([@[Carry Hrs]]>=8,0,8-[@[Carry Hrs]]))
2. For OT:

=IF([@[LO OUT]]="","",IF([@[Carry Hrs]]>=16,0,IF(SUM([@[LO Hours]],[@[Carry Hrs]])>=14,MAX(0,14-[@[Carry Hrs]]),IF(SUM([@[Carry Hrs]],[@[LO Hours]])>8,MIN([@[LO Hours]]-[@Straight],SUM([@[Carry Hrs]],[@[LO Hours]])-8)))))

3. for DT: =IF(SUM([@[LO Hours]],[@[Carry Hrs]])<14,0,[@[LO Hours]]-SUM([@Straight],[@OT]))
Let us know if you have any questions.

[jehster1]

Thanks for the response JeteMC! Sorry for the delayed response, whole house got the flu. I'll play with it this week and hopefully it works. I'll update the post if it does.

thanks again!!