Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering Numbers
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering Numbers
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 "ZachB" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering Numbers
That first one worked, thank you so much!
Does anyone know how I can make excel recognize those numbers as times? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filtering Numbers
try
=time(int(data/100),mod(data,100)) and format appropriately "ZachB" wrote: That first one worked, thank you so much! Does anyone know how I can make excel recognize those numbers as times? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filtering out text from numbers | Excel Discussion (Misc queries) | |||
Filtering numbers in a column? | Excel Worksheet Functions | |||
Filtering numbers???? | Excel Worksheet Functions | |||
Excel 2002 - Filtering numbers | Excel Discussion (Misc queries) | |||
extracting numbers with no more than 8-digits using advanced filtering | Excel Worksheet Functions |