Overtime pay calculation

[DarStro4]

Hello,

   I need help with a custom excel spreadsheet for timesheets. There spreadsheet need to calculate in a way 4 -5 different pays. What it needs to calculate is when a person works over 9 hours in a single day those hours past 9 go into overtime. When the total amount of hours go past 70 total (and I should note that this could happen while in the middle of a shift) the rest of those hours go into overtime. Then any additional hours worked past 70, per day then need to have the calculation in a different column following the same order that  up to 9 in one column and anything past 9 in the other column.

So in the spreadsheet that I have been creating, there are 4 columns

1- Regualr hours (Under 70 total, less the 9 per day)

2 - Regular over 9 (Under 70 total, beyond 9 per day)

3 - OT under 9 (Hours past 70 total, but under 9 per day)

3 OT Past 9 (Hours past 70 total, but beyond 9 per day)

This is a very rare kind of timesheet that I am having issue with making. specially when it comes to the OT.

[#2]

@DarStro4, to help us devise a solution for you, please attach your sample file with hard-coded results.

[#3]

@DarStro4 Perhaps the attached workbook can guide you to a solution for your own time sheet. I tested all sorts of hours and it seems to work as you described it. It even works if an employee works less than the regular 9 hours per day.