how do you format cells by time?

[Guest]

I am trying to sort times by earliest to latest on excel.
EX: I want this 8:30am-9:30am
9:45am-12:00pm
12:45pm-1:30pm
2:00pm-3:45pm

I have this 2:00pm-3:45pm
9:45am-12:00pm
8:30am-9:30am
12:45pm-1:30pm
I have them mixed up, but how do I format them to work in ascending order by
time??

 

[Bob Phillips]

You need to create a helper column to extract the end time into, and sort on
that column.

This will extract the time

=TIME(MID(A1,FIND("-",A1,4)+1,FIND(":",A1,4)-FIND("-",A1,4)-1),MID(A1,FIND("
:",A1,4)+1,2),0)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Sylvia-

If each line of the content you posted is stored in a single cell, you do
_not_ have times stored as data, you have text strings. Therefore, neither
A-Z or Z-A will give you the order you want.

One option is go to Tools>Options>Custom Lists and create a Custom List
based on the content of your column. Once defined, the custom list can be
used for sorting if you click in the appropriate column and go to Data>Sort.
Click the 'Options' button in the lower left corner of the dialog box, open
the 'First key sort order' list & choose your custom list. Then click OK in
each dialog box.

HTH |:>)

 

[Guest]

How about 24 hour time (0:00 = midnight, 6:00 = 6 a.m., 12:00 = noon, 18:00 =
6 p.m.). In your example, it would be 08:30-09:30
09:45-12:00
12:45-13:30 (add 12 to
the hour)
14:00-15:45

Hope this helps,

Carole O