if AND statement. Displays ERROR

[tbarnes22]

IF(AND(SEARCH("U",$G25),AND(TIMEVALUE("12:00")>=$H25,TIMEVALUE("12:00")<$I25)),"x"," ")

I want the target cell to display x if a time falls in between a time range, and it works fine. The problem is when I try combining the two formulas. I'm trying to make it create an x if "U" is found in a certain cell AND a time falls between a certain range. Both work fine on their own, but when I combine the two formulas into one AND statement I find trouble.

The formula works for when it meets the condition, and creates an x. The problem is that if it doesn't meet the conditions it creates an error rather than the space. I have temporarily resolved this by using:
=IFERROR(IF(AND(SEARCH("U",$G13),AND(TIMEVALUE("23:30")>=$H13,TIMEVALUE("23:30")<$I13)),"x"," ")," ")

But that is not ideal.

I would be extremely grateful for assistance on the problem!

[Pete_UK]

The problem is that SEARCH returns an error if the letter doesn't exist, so the way to get round it is like this:

IF(ISERROR(SEARCH("U",$G25)),"",IF(AND(TIMEVALUE("12:00")>=$H25,TIMEVALUE("12:00")<$I25),"x",""))

or you could do it like this:

IF(ISNUMBER(SEARCH("U",$G25)),IF(AND(TIMEVALUE("12:00")>=$H25,TIMEVALUE("12:00")<$I25),"x",""),"")

Hope this helps.

Pete

[tbarnes22]

Thanks Pete, you are absolutely amazing! This solved my issue, thank you!

[dangelor]

A failed SEARCH will always return a #VALUE error (see Excel's help). Try using the IFERROR in front of the SEARCH function to return FALSE...

=IF(AND(IFERROR(SEARCH("U",$G25),FALSE),AND(TIMEVALUE("12:00")>=$H25,TIMEVALUE("12:00")<$I25)),"x"," ")

[tbarnes22]

A failed SEARCH will always return a #VALUE error (see Excel's help). Try using the IFERROR in front of the SEARCH function to return FALSE...

=IF(AND(IFERROR(SEARCH("U",$G25),FALSE),AND(TIMEVALUE("12:00")>=$H25,TIMEVALUE("12:00")<$I25)),"x"," ")

Thanks for your reply dangelor, that will definitely help me in the future.