HELP!! Formula with different conditions

  • Thread starter Thread starter Teri
  • Start date Start date
T

Teri

Hi there. I'm hoping someone can help me. I'm pulling my hair out trying to
figure out how I can take a certain number of hours (for OT banking purposes)
and calculating anything over 7.5 hours, up to 11 hours in one column - and
anything over 11 hours in a separate column.

For example: An employee worked 15 hours in a day. In the first column the
formula would provide the number 3.5 (as 7.5 plus 3.5 is 11 hours for the
day) and the second column would provide the number 4 (as this is the number
of hours over the 11 hours).

Any help that anyone could provide would be GREATLY appreciated!

Thanks so much!!

Teri
 
Suppose your 15 is in A1. This will give you 3.5 in one column:

=MAX(0,MIN(11,A1)-7.5)

and this will give you 4 in another column:

=MAX(0,A1-11)

and will cope with other values as expected.

Hope this helps.

Pete
 
Try these...

A2 = total hours worked

B2 = Reg hours
=MIN(7.5,A2)

C2 = Hours >7.5 but <=11
=IF(A2>11,3.5,MAX(0,A2-7.5))

D2 = Hours >11
=MAX(0,A2-11)
 
Assuming that the 11 is in cell A2.

First number, hours over 7.5, but less than 11:
=MAX(0,MIN(A2,11)-7.5)

Hours over 11:
=MAX(A2-11,0)
 
Back
Top