filter list of times Excel 2003
 

If you have a list of times eg:
00:00
00:15
00:30
00:45
01:00
etc, how can you sort them so you can only see those ending in 0? Have
tried a range of filters and also an IF statement, but format keeps showing
full hh:mm:ss in formula bar when I do either of these, despite having set up
a custom format for hh:mm, which still shows as cell format in Format Cell
menu. Any help much appreciated

filter list of times Excel 2003
 

In a helper column enter the formula:

=IF(ROUND(A3/"00:10",0)*"00:10"=A3,1,0)
and copy down. Then filter on 1

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"DonnaO" wrote in message
...
If you have a list of times eg:
00:00
00:15
00:30
00:45
01:00
etc, how can you sort them so you can only see those ending in 0? Have
tried a range of filters and also an IF statement, but format keeps
showing
full hh:mm:ss in formula bar when I do either of these, despite having set
up
a custom format for hh:mm, which still shows as cell format in Format Cell
menu. Any help much appreciated

filter list of times Excel 2003
 

Alternative returning TRUE/FALSE

=ROUND(MINUTE(A3)/2,0)*2=MINUTE(A3)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sandy Mann" wrote in message
...
In a helper column enter the formula:

=IF(ROUND(A3/"00:10",0)*"00:10"=A3,1,0)
and copy down. Then filter on 1

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"DonnaO" wrote in message
...
If you have a list of times eg:
00:00
00:15
00:30
00:45
01:00
etc, how can you sort them so you can only see those ending in 0? Have
tried a range of filters and also an IF statement, but format keeps
showing
full hh:mm:ss in formula bar when I do either of these, despite having
set up
a custom format for hh:mm, which still shows as cell format in Format
Cell
menu. Any help much appreciated

filter list of times Excel 2003
 

"Bob Phillips" wrote in message
...
Alternative returning TRUE/FALSE

=ROUND(MINUTE(A3)/2,0)*2=MINUTE(A3)

Except that 0:16 would trurn TRUE whereas the OP only wants time ending in 0

--
Regards

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk