Making my own timesheet to keep track of pay and hours done

Hi all,

 

So I'm making a personal timesheet from, nearly, scratch and I need a hand with one problem I'm having.

 

I'm a casual worker and have two different levels of overtime, 1.5x and 2x for which there is a column each. 1.5x hits when I do a shift over 12 hours and stops at 3 hours over that, and the first 3 hours of every shift after 84. The 2x hits when a shift is over the initial 3 hours of overtime and after the first 3 hours of every shift after 84 hours.

There are also two occasions when I can hit overtime, obviously, 1st is when I do a shift over 12 hours and 2nd is when my total hours for the fortnight go over 84 hours. I have the 1st instance done, which is easy enough, however the 2nd one I'm struggling with. It's not so much shifts that hit after the 84, its the shift that hits 84 hours I can't get it so that it puts the hours before 84 in my regular hours. I've included a copy of the table and pointed the problem out.

 

I am self-taught, and found the initial table online however most of its been changed, so a lot of the formulas are most likely overly long and more complicated than they need to be and any help with this would also be greatly appreciated. I've been stuck on this one problem for a week now and I just can't get it.

 

Regards,

Kelly.