If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
HELP!! Formula with different conditions
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 |
#3
|
|||
|
|||
HELP!! Formula with different conditions
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 |
#4
|
|||
|
|||
HELP!! Formula with different conditions
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 |
#5
|
|||
|
|||
HELP!! Formula with different conditions
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 |
#6
|
|||
|
|||
HELP!! Formula with different conditions
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 |
Thread Tools | |
Display Modes | |
|
|