Calculating ROP Based On Hours Worked

I have been racking my brain on trying to figure out a formula to help me -but being new to formulas, I just can't figure it out, so you are my last hope.

I am trying to calculate the ROP that an employee works based on their start and stop times. To throw a wrench into the mix, there could be different rates based on how many people work with this employee, and of course, if they work overtime, I have to take that into consideration.

ForemanPay.JPG


This employee worked total day hours of 10.5. He took a unpaid 1/2 lunch, which means we are to pay him for 10 hours (8regular 2 ot). So based on the information from above, his ROP should work out to this:

6 Hours @ $37.99
2 Hours @ $37.31
1 Hour OT @ 55.96
1 Hour OT @ 54.00

So my question is if there is a formula that I can use in Excel that can automatically figure out the ROP & OT?

Your help is much appreciate and I have attached a sample for you to play with.

Posted to wrong forum sorry!