if and + formula

[kevcar40]

hi
the following formula allows me to adjust the start time of an error
and it works fine

=IF(AND(P30<$S$27,P30<$S$27),"06:00:00",TEXT(MOD(P30,1),"hh:mm:ss"))

what i am trying to do is ammend the formula to allow me to check
another cell for a day

cell n 30 = Saturday

=IF(AND(P30<$S$27,P30<$S$27),"06:00:00",TEXT(MOD(P30,1),"hh:mm:ss"),
if(n30 = "Saturday"))
this is not working
can anyone point me in the right direction please

thanks kevin

 

[Pete_UK]

Well, what is it that you want to do if N30 is Saturday?

I think the first part of your formula is a bit redundant - this
should do the same job:

=IF(P30<$S$27,"06:00:00",TEXT(MOD(P30,1),"hh:mm:ss"))

Hope this helps.

Pete

 

[David Biddulph]

It's not clear what you are trying to do with your Saturday test.

Each IF statement has 2 or 3 parameters. The syntax is
IF(condition,result_if_condition_true,result_if_condition_false). If you
omit the result_if_condition_false parameter, then it will default to
returning a logical value of FALSE if the condition is not met.
If you want to nest IF functions you can do that by replacing one or other
of the result parameters (or both) by a further IF statement. The most
frequent syntax would be
=IF(first_condition,result_if_first_condition_true,IF(second_condition,result_if_second_condition_true,result_if_second_condition_false));
you won't get to test the second condition at all if the first condition is
true.
In your proposed revised formula you've already given the first IF statement
its 3 parameters, so it has no way of going on to the 2nd IF (which in any
case has no defined result).
Another option is to combine conditions with an AND or OR function (as you
triied to do in your initial formula, but see below).

I notice a couple of things about your existing formula:
Firstly the 2 parameters for the AND function are identical, so the function
is doing nothing different from having the single condition P30<$S$27
Secondly the results you are returning are both text strings, so yo won't be
able to do further calculations with the result.
[And similarly in the suggested Saturday test you are testing for the text
string "Saturday", not for a date which falls on a Saturday; if the latter
is what you want, you could use =IF(TEXT(N30,"dddd")="Saturday",... or use
the WEEKDAY function.]

 

[kevcar40]

It's not clear what you are trying to do with your Saturday test.

Each IF statement has 2 or 3 parameters. The syntax is
IF(condition,result_if_condition_true,result_if_condition_false). If you
omit the result_if_condition_false parameter, then it will default to
returning a logical value of FALSE if the condition is not met.
If you want to nest IF functions you can do that by replacing one or other
of the result parameters (or both) by a further IF statement. The most
frequent syntax would be
=IF(first_condition,result_if_first_condition_true,IF(second_condition,resu­lt_if_second_condition_true,result_if_second_condition_false));
you won't get to test the second condition at all if the first condition is
true.
In your proposed revised formula you've already given the first IF statement
its 3 parameters, so it has no way of going on to the 2nd IF (which in any
case has no defined result).
Another option is to combine conditions with an AND or OR function (as you
triied to do in your initial formula, but see below).

I notice a couple of things about your existing formula:
Firstly the 2 parameters for the AND function are identical, so the function
is doing nothing different from having the single condition P30<$S$27
Secondly the results you are returning are both text strings, so yo won'tbe
able to do further calculations with the result.
[And similarly in the suggested Saturday test you are testing for the text
string "Saturday", not for a date which falls on a Saturday; if the latter
is what you want, you could use =IF(TEXT(N30,"dddd")="Saturday",... or use
the WEEKDAY function.]
--
David Biddulph

hi
the following formula allows me to adjust the start time of an error
and it works fine

=IF(AND(P30<$S$27,P30<$S$27),"06:00:00",TEXT(MOD(P30,1),"hh:mm:ss"))

thanks both for your help
i'll give it a go now