how do you format cells by time?
 

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??

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)


"Sylviags" wrote in message
...
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??

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 ToolsOptionsCustom 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 DataSort.
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 |:)

"Sylviags" wrote:

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??

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

"Sylviags" wrote:

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??