Formula to remove invalid times?

**russianspd** · 10-07-2011, 02:51 PM

I'm trying to get rid of times that aren't in the proper 24 hour format. Every first flight # of a group of flight #'s is the correct one, outlined in red below. I need to remove all of the rest of the false strings with improper time information. I have attatched a CSV file with the info. Example:
23,JFK,GVA,19:35pm,9:40am,8:05,333,3857
23,JFK,GVA,31:35pm,9:40am,8:05,333,3857
23,JFK,GVA,43:35pm,9:40am,8:05,333,3857
17,JFK,ZRH,17:50pm,8:05am,8:15,333,3925
15,JFK,ZRH,20:55pm,10:50am,7:55,333,3925
15,JFK,ZRH,32:55pm,10:50am,7:55,333,3925
15,JFK,ZRH,44:55pm,10:50am,7:55,333,3925
22,GVA,JFK,12:10pm,14:50pm,8:40,333,3857

The colored strings are the correct strings and is what I need to isolate in the rest of the data, everything between that I need to either completely erase/bypass. Hopefully I will be left with all of the flights with times within the 24hr time format

**TMS** · 10-07-2011, 02:57 PM

In cell I2, put the formula:

=IF(A2=A1,"Delete","")

and fill down.

Then filter on column I for "delete"

Delete the visible rows.

Regards

**russianspd** · 10-07-2011, 03:06 PM

Just want to clarify i'm trying to delete the rows with the times that are not in 24 hour format. The formula will do so?

**tlafferty** · 10-07-2011, 03:06 PM

Use this formula in helper columns, then filtere for those records that are TRUE (not 24 hr time)

Please Login or Register  to view this content.

.

**TMS** · 10-07-2011, 03:18 PM

You said the first in a sequence was valid, subsequent entries are invalid. If that's the case, this helper formula will do that. It will generate "delete" whenever the entry in column A is repeated. Filter on that and do as instructed ... delete them.

Regards

**russianspd** · 10-07-2011, 03:25 PM

Maybe I worded it wrong. Row 1 is correct. Rows 2-18 are incorrect because the times located in columns D and E are not in 24hour format. The next flight # which is 17 in row 19 is correct. Flight 15 for row 20 is correct but rows 21-31 are not because of the time issue again. Can I change the helper formula and then filter to only select rows with times in 24hour format?

Thanks for your help

**TMS** · 10-07-2011, 03:38 PM

Have you actually put either of the formulae into your spreadsheet and checked the results?

See the example.

Regards

**russianspd** · 10-07-2011, 03:41 PM

Using Tlefferty's method it did work, I appreciate BOTH of you guy's help. I had been struggling with this issue for a couple days. I take it you but in both formulas? One in clumn I and J? I'll give that a try

**TMS** · 10-07-2011, 05:25 PM

I'm a little concerned that there are strange times in column E. Are they acceptable?

Whatever, if you have a solution that meets your needs, our work is done

If this has answered your question, or at least pointed you in the right direction, please mark your thread as solved. See my signature for details or the FAQ.

Regards