Filtering Numbers


G

Guest

I have a file with times in a column listed in military format, without a
colon, and in 15 minute intervals (115, 130, 145, 200, 215... 2100, 2115,
etc.). I need only the hourly data so I need to filter out everything that
doesn't end in two zeros. I've been trying to do this with the autofilter
with no success.

I've also been trying to make the data recognizable as a time format with no
luck, perhaps solving this first would make the previous problem easier.
Thanks for any help!

Zach
 
Ad

Advertisements

G

Guest

I would use a helper column
if it is truely numeric, I would use
=if(int(A1/100)=int(A1)/100,1,0)
if it is text
=if(right(A1,2)="00",1,0)
(use the appropriate column designator)
copy and paste down as far as you need
use auto filter on this column and select all the ones
 
G

Guest

That first one worked, thank you so much!

Does anyone know how I can make excel recognize those numbers as times?
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top