Filtering Numbers

[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

 

[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

 

[Guest]

That first one worked, thank you so much!

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

 

[Guest]

try
=time(int(data/100),mod(data,100))
and format appropriately