How to return a result when searching multiple criteria including date range

[Jonnyd74]

I am trying to return a value when searching multiple criteria including a date range. For example-
Column A= Time of Error
Column B= Description of Error
Column C= Would like Return of Error Message 500 only during a certain time.

A1 05/10/2016 12:00 B1- Customer Receives Error 500
A2 05/10/2016 1:00 B2- Customer Receives Error 500
A3 05/10/2016 2:00 B3- Customer Receives Error 500
A4 05/10/2016 3:00 B4- Customer Receives Error 500

In Column C, I would like it to return "Customer Receives Error 500" only if it was between 5/10/2016 1:00 and 5/10/2016 2:00, otherwise it would return "Different Issue"

This will be an ever changing spreadsheet with data constantly added with other errors, so would prefer to avoid Advance Filtering.



Thanks much!

 

[Joe4]

Welcome to the Board!

This will be an ever changing spreadsheet with data constantly added with other errors

So, does your date check also need to change/shift?
Are you really concerned about the date portion at all, or just the time piece of it?

 

[Jonnyd74]

Hi Joe4.
The date check will not need to change/shift for now. (Once I am able to figure out the formula, I can adjust it as needed)
I am concerned about the date portion as well, as I only posted a small sample of an issue that occurred across more then 1 day.

I would imagine the formula would include some kind of IF AND search function such as =IF((AND(A1<=5/10/2016 1:00,A1>=5/10/2016 2:00,(IF(ISNUMBER(SEARCH "Error 500",B1)), "Customer Receives Error 500","Different Issue")))

Sorry, that formula is way off, but hopefully will be a good starting point (and not any confusion )


Appreciate the quick reply!

 

[Joe4]

Enter the date and times you want in some static unused cells, i.e.
Y1: 5/10/2016 1:00
Z1: 5/10/2016 2:00

Then you can use:

=IF(AND(A1>=$Y$1,A1<=$Z$1,ISNUMBER(SEARCH("Error 500",B1))),"Customer Receives Error 500","Different Issue")

Note that you can have more than 2 arguments in the AND function, and I think you also had your original greater than and less than signs backwards.

 

[Jonnyd74]

Yes!!! worked perfectly. Thank-you! That's a huge help

Follow up question, is it possible to add in that formula another condition, such as "Error 500" or "Error 503" with everything else being equal. I would think it would involve a "OR" formula but cant get it to work in conjunction with the "AND" formula.

 

[Joe4]

Are there any other errors that start with number "50" that you do NOT want as part of the formula?
If not, just change the search part to look for "Error 50" instead of "Error 500". That would capture both 500 and 503 error codes.

 

[Jonnyd74]

Unfortunately not, the other errors are completely different then the Error 500 such as "Lost Connection Error"

 

[Joe4]

Unfortunately not, the other errors are completely different then the Error 500 such as "Lost Connection Error"

But what error number is it?
As long as you don't have any errors using number 50,501,502,504,505,506,507,508, or 509, it may work.

However, one other thing I did not ask about, what error message do you want return for a 503 error?

 

[Jonnyd74]

It may not be an error number, it could be a general error such as "Lost Connection Error"
So what I am looking for are 3 errors that occurred during a certain time frame, that are all symptoms of the same root cause, Originally I used the return value of "Customer Receives Error 500" for simplicity sake. The return value will actually be "Occurred due to Global Product Outage"

 

[Joe4]

OK. You need to break this down for me.
Please list the different scenarios, and what you want to happen in each one.