Sort According to Time irrespective of the date.

I have this bunch of data. there are other values to the right of it. But I wanna sort by all the values at 11:00 a.m only. So is there a way to do this.

For example; (please note the below each Date and time are in a single cell in excel)

Date & Time
4/21/2009 11:00
4/21/2009 12:00
4/21/2009 13:00
4/21/2009 14:00
4/21/2009 15:00
4/21/2009 16:00
4/21/2009 17:00
4/21/2009 18:00
4/21/2009 19:00
4/21/2009 20:00
4/21/2009 21:00
4/21/2009 22:00
4/21/2009 23:00
4/22/2009 0:00
4/22/2009 1:00
4/22/2009 2:00
4/22/2009 3:00
4/22/2009 4:00
4/22/2009 5:00
4/22/2009 6:00
4/22/2009 7:00
4/22/2009 8:00
4/22/2009 9:00
4/22/2009 10:00
4/22/2009 11:00
4/22/2009 12:00
4/22/2009 13:00
4/22/2009 14:00
4/22/2009 15:00
4/22/2009 16:00
4/22/2009 17:00
4/22/2009 18:00
4/22/2009 19:00
4/22/2009 20:00
4/22/2009 21:00
4/22/2009 22:00
4/22/2009 23:00
4/23/2009 0:00
4/23/2009 1:00
4/23/2009 2:00
4/23/2009 3:00
4/23/2009 4:00
4/23/2009 5:00
4/23/2009 6:00
4/23/2009 7:00
4/23/2009 8:00
4/23/2009 9:00
4/23/2009 10:00
4/23/2009 11:00
4/23/2009 12:00
4/23/2009 13:00
4/23/2009 14:00
4/23/2009 15:00
4/23/2009 16:00
4/23/2009 17:00
4/23/2009 18:00
4/23/2009 19:00
4/23/2009 20:00
4/23/2009 21:00
4/23/2009 22:00
4/23/2009 23:00
4/24/2009 0:00
4/24/2009 1:00
4/24/2009 2:00
4/24/2009 3:00
4/24/2009 4:00
4/24/2009 5:00
4/24/2009 6:00
4/24/2009 7:00
4/24/2009 8:00
4/24/2009 9:00
4/24/2009 10:00
4/24/2009 11:00
4/24/2009 12:00
4/24/2009 13:00
4/24/2009 14:00
4/24/2009 15:00
4/24/2009 16:00
4/24/2009 17:00
4/24/2009 18:00
4/24/2009 19:00
4/24/2009 20:00
4/24/2009 21:00
4/24/2009 22:00
4/24/2009 23:00
4/25/2009 0:00
4/25/2009 1:00
4/25/2009 2:00
4/25/2009 3:00
4/25/2009 4:00

I wish to have it look something lik
4/21/2009 11:00
4/22/2009 11:00
4/23/2009 11:00
4/24/2009 11:00
4/25/2009 11:00
other values follow here.

Thanks a lot.

Awesome Pjoaquin... but a small glitch..
When i sort, it shows first 12:00 a.m, 1:00 a.m, 2:00 a.m etc cos day begins at 12:00 a.m rite. But is there a way to have it started from 11:00 a.m or 5:00 p.m or simply put in a custom time rather than have it starting at 12:00 a.m.

Thanks a ton.

Hey thanks martin.. that worked as well. I have Office 07 on my laptop. I shall try with 03 and check back here cos thats wat i am suppose to use.

Originally Posted by martindwilson

cant you just auto filter on time

I tried wat martin said. It worked like a charm in Office 07 but in Office 03 it gives me a error saying "Text you entered is not a valid reference of defined name" when I enter "Time" in the "Then by" drop down list.

This is the sample Input Data.


Date & Time
4/21/2009 11:00
4/21/2009 12:00
4/21/2009 13:00
4/21/2009 14:00
4/21/2009 15:00
4/21/2009 16:00
4/21/2009 17:00
4/21/2009 18:00
4/21/2009 19:00
4/21/2009 20:00
4/21/2009 21:00
4/21/2009 22:00
4/21/2009 23:00
4/22/2009 0:00
4/22/2009 1:00
4/22/2009 2:00
4/22/2009 3:00
4/22/2009 4:00
4/22/2009 5:00
4/22/2009 6:00
4/22/2009 7:00
4/22/2009 8:00
4/22/2009 9:00
4/22/2009 10:00
4/22/2009 11:00
4/22/2009 12:00
4/22/2009 13:00
4/22/2009 14:00
4/22/2009 15:00