Prev: Extract data from csv file
Next: Cell content capture and release
From: Teri on 17 Feb 2010 12:15
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!!
From: Paul C on 17 Feb 2010 14:17
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
--
If this helps, please remember to click yes.


"Teri" wrote:

> 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!!
 | 
Pages: 1
Prev: Extract data from csv file
Next: Cell content capture and release