Filtering times in Excel 2007

[Guest]

Hi,

I have a list of times, automatic data was taken every 15 seconds (I have
over 5,000 rows of data).

I need to show only times that end in 00, so a whole minute.

Column A
row 1 Time
row 2 00:00:00
row 3 00:00:15
row 4 00:00:30
row 5 00:00:45
row 6 00:01:00

In this example only rows 2 and 6 would be returned.

Any suggestions.

Thanks

 

[Guest]

The easiest solution here would probably be to use a helper column. Insert a
new column for B and use this formula and copy down:

=IF(SECOND(A2)=0,"X","")

This will return an "X" for any value in column A that ends in :00. Then,
apply the filter to Column B to show only "X" values.

The other option (although more cumbersome in my opinion) would be to
convert all of the times in Column A to Text, then use the Custom Filter to
show values that end with 00.

HTH,
Elkar

 

[Dave Peterson]

I would insert a helper column (a new column B) and use a formula like:
=SECOND(A2)
and drag down.

Then filter by that column to show 0.

 

[Guest]

Thanks Elkar.

The easiest solution here would probably be to use a helper column. Insert a
new column for B and use this formula and copy down:

=IF(SECOND(A2)=0,"X","")

This will return an "X" for any value in column A that ends in :00. Then,
apply the filter to Column B to show only "X" values.

The other option (although more cumbersome in my opinion) would be to
convert all of the times in Column A to Text, then use the Custom Filter to
show values that end with 00.

HTH,
Elkar