Excel If Statement with Wildcards and Value Search

Question

I want to categorise a lot of data that includes flight arrival and departure times. The data can include statements like "On Time," "34 Minutes Early," "7 Minutes Late," etc. I only want to categorise whether the flight was on time, early, or late. To accomplish this, the formula shown below has been effective:

=IF(ISNUMBER(SEARCH("*Late*",E3)),"Late",IF(ISNUMBER(SEARCH("*Early*",E3)),"Early",IF(ISNUMBER(SEARCH("*On Time*",E3)),"On Time","")))

Yet since my analysis serves a useful function, I'd want to define "on time" as everything that arrives or departs within 10 minutes of its scheduled time. The following formulas are some of the versions I've attempted.

=IF(COUNTIF(E19,"*<10*"),"On Time","") =IF(COUNTIF(E19,"<"&"*10*"),"On Time","") 

Which would then be put into my nested IF Statement above, but nothing seems to work.

Answer 1

Try this:

IFERROR(IF(IFERROR(LEFT(B2,2)*1,"")<=10,"",IFERROR(LEFT(B2,2)*1,""))*IF(IFERROR(SEARCH("*lat*",B2,1),0)=1,-1,1),"")

See: