more than one IF statement for a cell?

[neowok]

i have tihs IF statement as the formula in a cell

=IF((W11="N/A"),"N/A",(U11-S11+IF(S11>U11,1))-W11)

what i want to add to this is that in addition to the current i
statement, if (U11-S11+IF(S11>U11,1))-W11) is <0 then set this cell t
"00:00" rather than the "N/A" in the existing If statement.

i am unsure how to do this though.

=IF((W11="N/A"),"N/A",(U11-S11+IF(S11>U11,1))-W11)
IF(U11-S11+IF(S11>U11,1))-W11)<0,"00:00")

this would perform the first if and then also after doing that, perfor
the second if which sets the cell to 00:00 if the result of tha
calculation is <0. but putting that into the cell it seems to read th
thing as text and not a formula.

at the moment it reads, if W11 is "N/A" then set this cell to "N/A
else set this cell to the result of that calculation. what i need the
is if the result of this calculation produces a negative number, se
the contants of this cell to "00:00" instead, because otherwise i
comes out as ######

 

[Tom Ogilvy]

=IF((W11="N/A"),"N/A",IF((U11-S11+IF(S11>U11,1))-W11<0,0,(U11-S11+IF(S11>U11
,1))-W11))

--
Regards,
Tom Ogilvy

what i want to add to this is that in addition to the current if
statement, if (U11-S11+IF(S11>U11,1))-W11) is <

 

[neowok]

tried that but its now setting the cell to 0 on absolutely everything
including when W11 is "N/A" or not.

strange because it shouldnt even be going into setting it to 0 if W1
is N/a should it? hmmmmm

=IF((W11="N/A"),"N/A",IF((U11-S11+IF(S11>U11,1))-W11<0,0,(U11-S11+IF(S11>U11,1))-W11))

thats the code thats somehow setting it to 0 regardless of anything.
looks kinda like it should work to me but isnt for some reason.

Thank

 

[Tom Ogilvy]

It works fine for me.

Apparently W11 does not actually equal N/A. You might have an extra space
in there or something.

I tested all three conditions and it returned what I expected.

 

[neowok]

W11 can contain a time OR an "N/A".

if W11 contains a time then the cell with this formula should do th
calculation (U11-S11+IF(S11>U11,1))-W11. if the result of that is <
then this cell shold be set to 0, if the result is not <0 then the cel
should contain whatever the result of that calculation is.

else if W11 doesnt contain a time then the cell with this formul
should be set to 0.

at the moment W11 contains a time but the result of the calculatio
(U11-S11+IF(S11>U11,1))-W11 IS <0 so the cell should be set to 0. I
does set the cel to zero, but its also setting EVERY other cell that
put this formula in to 0, even when it doesnt contain "N/A" and isn
<0

some sample values taken straight from my sheet, with this formul
going in column X and all columns set to type hh:mm

planned actual planned acual hours hours
time time time time worked los
(hours:mins)
on on finish finish

S T U V w
X
00:00 00:25 08:00 08:00 07:35 00:25
00:00 00:15 08:00 08:30 08:15 #####
00:00 00:00 06:30 06:30 06:30 00:00
00:00 00:00 05:30 0530 05:30 00:00
21:00 21:30 03:30 07:30 07:30 #####
23:00 N/A 05:30 N/A N/A N/A

that is the result of using my formul
=IF((W10="N/A"),"N/A",(U10-S10+IF(S10>U10,1))-W10)

in column X, the problem im trying to solve is when hours lost ends u
negative (i.e. hours have actually been gained) then it sets hours los
to 0 instead of the ## which denotes a negative value. everything els
is fine with the current formula.

im usin
=IF(NOT(OR(ISNUMBER(T10),ISNUMBER(V10))),"N/A",V10-T10+IF(T10>V10,1))

in column W to calculate the number of hours worked and this part work
perfectly.

using toms formula in X, the value of every cell in X ends up as 00:0
for some reason

thank

 

[Tom Ogilvy]

Using my formula on your data I get

0:25
0
0
0
0
N/A


for
row 10 produces the result of your calculation since it is > 0
row 11 produces 0 since the result of your formula is < 0
row 12 produces 0 since the result of your formula is 0
row 13 produces 0 since the result of your formula is 0
row 15 produces 0 since the result of yor formula is < 0
row 16 produces N/A

As I said, the formula does exactly what you say you want it to do.

 

[neowok]

not sure what went wrong before but its working now

thank