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, 2023 in Others 398 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),"")
```

See:

answered Apr 2, 2023 by
• 38,010 points

## Excel Formula with Nested IF/LEFT/AND Functions

Use this: =IF(SUMPRODUCT(--(LEFT(G3,1)={"1","2","3"}))>0,"998", ...READ MORE

## 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

## Excel VBA if file closed, then open and paste, else just paste data

Slightly re-worked to add full workbook/sheet qualifiers ...READ MORE

## Excel - IF Formula with a FIND

What about using mid() to see if ...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