0

Hey I've been trying to find a way to delete the rows I need via formula without having to do change to much before it works.

When I download CSV, it stores time/date like this:

08:56  29-Sep
07:56  29-Sep
07:15  29-Sep
03:29  29-Sep
09:15  29-Sep
05:15  29-Sep
09:30  29-Sep
06:15  29-Sep

Each one being on a different row.

What I'm trying to do is delete certain rows based on a set criteria.

The criteria being Before 09:00 on today's date, and after 05:00 on tomorrow's date.

Is this possible without splitting the data up?

I've been trying to do it via IF statement, but don't use excel for much more than the basics to be honest.

teylyn
  • 34,374
  • 4
  • 53
  • 73
1sbro
  • 1

2 Answers2

1

Teylyn gave a TLDR while I was typing.

The longer answer, like Teylyn suggested is to flag the data.

You can use the HOUR() and ROUND() functions to extract the date/time from the string, then simply perform a simple next And/Or logic IF statement to flag yes/no for the removal.

=IF(OR(D2<0,AND(D2=0,C2<9),AND(D2=1,C2>17),D2>1),"REMOVE","KEEP")

Where column C is the extacted hour ( in 24h format ) and D is days offset from 'current' date using the following

=ROUND(A2,0)-TODAY()

Here's an example worksheet : www.instantexcel.com/downloads/stackexchange/64235111.xlsx

And a screenshot example :Multiple criterea if statement

Instant Excel . com
  • 51
  • 3
0

There are several ways to achieve this.

  • load all the CSV data into Excel, then sort the data and delete the rows you don't need
  • load all the CSV data into Excel, then create a formula that flags rows you don't need, then filter the data based on that flag. This will not remove any data, only hide it from view.
  • load the data into Power Query instead of the worksheet and clean it up in Power Query before loading it to the worksheet.

Formulas cannot delete data. Formulas can help you identify data, and then you can use that for sorting or filtering data.

teylyn
  • 34,374
  • 4
  • 53
  • 73