Keep only 7am to 7pm data from 24hr monthly dump - 170k rows!

[essee]

Hi guys,

I have a strange one for you here, if anyone fancies helping me, please?

I have a 24 hour dump of data for the entire month but only require what is in the 7am to 7pm window. It's 170+ thousand rows!

What is the best way to do this?

Many thanks,
David.

(I've had to cut it down to two days, because the file was way too big)
Test Data3.xlsx

[Pete_UK]

You could put a formula like this in I2:

=IF(OR(MOD(B2,1)<--"07:00:00",MOD(B2,1)>--"19:00:00"),"remove","ok")

then copy it down by double-clicking the fill handle. Then apply autofilter to that column and select "remove". Highlight all the visible rows and click the <delete> icon. Then remove autofilter from column I, and delete column I.

Hope this helps.

Pete

[essee]

Hi Pete,

Thanks for the speedy response. I was hoping for something with a few less steps, but that seems to work just fine. Beggars can't be choosers, I guess.

Much appreciated.

[essee]

It's just occurred to me, I need to remove the weekends too! Damn.

[Pete_UK]

Okay, change the formula to this in I2:

=IF(OR(WEEKDAY(INT(B2),2)>5,MOD(B2,1)<--"07:00:00",MOD(B2,1)>--"19:00:00"),"remove","ok")

Hope this helps.

Pete

[essee]

Yes, that's brilliant, thank you!

[Pete_UK]

Okay, glad to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete