HELP!! Formula for calculating overtime

T

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!!
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top