HELP!! Formula with different conditions

[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

 

[Pete_UK]

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

 

[T. Valko]

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)

 

[Luke M]

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)

 

[Teri]

Thanks so much EVERYONE!! IT WORKS!!!! )

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks so much EVERYONE!! IT WORKS!!!! )