HELP!! Formula for calculating overtime

[Teri]

I'm hoping someone can help me. I can't seem to figure out a formula to
calculate OT for my staff. Regular hours per day are 7.5. I'm looking to
create a formula for banking time which will break out in two columns hours
worked over the regular 7.5 hours per day up to 11 hours per day, and in the
second column the hours worked over 11 hours per day.

Does anybody have any ideas?

Thanks so much!!

 

[Paul C]

Something like this

Hours Bank1 Bank2
6 0 0
7.5 0 0
8.5 1 0
9.5 2 0
10.5 3 0
11.5 3.5 0.5
12.5 3.5 1.5
13.5 3.5 2.5
14.5 3.5 3.5
15.5 3.5 4.5


assuming the 6 is in cell A2 the formulas for b2 and c2 are as follows

b2 =IF(7.5<A2,MIN(A2-7.5,3.5),0)
c2 =IF(A2>11,A2-(7.5+B2),0)

Copy down as needed