Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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?? |
#2
|
|||
|
|||
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?? |
#3
|
|||
|
|||
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?? |
#4
|
|||
|
|||
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?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time format | Excel Worksheet Functions | |||
How do I get brackets in format cells accounting? | Excel Discussion (Misc queries) | |||
My sheet is unprotected, but I can't format cells. | Excel Discussion (Misc queries) | |||
time format multiplied by hours worked ? | Excel Worksheet Functions | |||
Subtracting time in date format | Excel Worksheet Functions |