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 |
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 |
Filtering Numbers
That first one worked, thank you so much!
Does anyone know how I can make excel recognize those numbers as times? |
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? |
All times are GMT +1. The time now is 02:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com