HELP!! Formula with different conditions

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

On Feb 17, 5:36*pm, Teri wrote:
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

Try these...

A2 = total hours worked

B2 = Reg hours
=MIN(7.5,A2)

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

D2 = Hours 11
=MAX(0,A2-11)

--
Biff
Microsoft Excel MVP


"Teri" wrote in message
...
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

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)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Teri" wrote:

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

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

"Teri" wrote:

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

You're welcome!

--
Biff
Microsoft Excel MVP


"Teri" wrote in message
...
Thanks so much EVERYONE!! IT WORKS!!!! )

"Teri" wrote:

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