Filter, Sort, Macro... what to do?

[zongle]

Hi, i'm trying to select and copy only certain rows of data. I've tried filtering but can't get it to work due to the date and time both being in one cell and wanting a range.

Basically I have a large amount of data with the date and time in the first column and then various things in the columns after it like this

I want to have say only only the rows which have morning data between 10:00 and 12:00.

It doesn't matter if it just copies to rows bellow the original data, copies it to a new sheet or just deletes the rows I don't want as I will just be copy and pasting it into another application.

If I try sorting or filtering I run into problems with the date and time both being in the same cell or looking for a specific value in a cell when i'm after a range of values and I don't have a clue about macros or anything else.

 

[Chrisdontm]

Try this;
Insert two columns to the left of Column A
So your column A will become column C
Then in A2 put: =C2 then format it for "Short Date"
Then in B2 put: =C2 then format it in the way of time that you want...
Then click on a cell in column B and click filter, and uncheck everything you don't want
Then copy and paste as " Values & Source Formatting " wherever you want it...
If you want, you could hide column C also...
I hope this helps...

 

[zongle]

Thanks that almost has it.

I now have a column that has just the time in it and I can filter only the times I want but it is only working if I individually uncheck the ones I don't want. Obviously with hundreds of entries that isn't practical.

So using the above data as an example I tried to do a Number Filter....Between.... with values of "Greater than 10:00:00" And "Less than 12:00:00" but rather than showing the three between those times it just gets rid of everything except the first entry.
It doesn't matter what values I use for the between filter it always gets rid of everything bar the first entry.

 

[Chrisdontm]

New Plan;

Add one column to the left of column " A "

Put this formula in the new " A2 ", and copy it on down;

=IF(AND(IF(AND(HOUR(B2)=12,MINUTE(B2)>0),FALSE,TRUE),HOUR(B2)-12<=0,HOUR(B2)-10>=0),1,0)

It will give you ones and zeros all the way down, then filter out the zeros and you should have what you want.

 

[zongle]

Thanks for the help. That is working better and these two macros from a cross post also get the job done Filter, Sort, Macro... what to do?.