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.

Apr 2 in Others 163 views

## 1 answer to this question.

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),"")
```
• 38,010 points

## How to search a file with a particular extension and a particulat string in its name?

You can easily find the file using ...READ MORE

## IF function in combination with an Round function Excel

I'm not sure if it is a ...READ MORE

## IF formula to compare a date with current date and return result

You can enter the following formula in ...READ MORE

## Excel Formula - if values in columns all contain X then return

You can use the AND function: = IF(AND(A3="OK";B3="OK";C3="OK");"everything ...READ MORE