Formatting time

[eponcedeleon]

I need to sort this column by time and the sort is not working. I have tried, changing the time to different I am not able to sort in 24hr format. Can anybody help



6:00 AM
12:30 PM
6:00 AM
1:30 PM
4:00 PM
7:00 AM
9:30 AM
8:00 PM
8:30 PM
5:00 PM
4:00 PM
7:30 AM
7:00 PM
6:00 PM
2:00 PM
11:30 AM
1:00 PM
7:00 AM

 

[Ottsel]

Sounds like a formatting issue, so lets look at your formatting first:
Select your data > Right Click > Format Cells > Select Numbers Tab at the top > Select Time in the Category List >Choose your ideal time format (e.g., "6:00 AM") > Apply settings
Next, sorting your data:
With your data selected go to the Data tab at the top > Click Sort A to Z or Z to A depending which way you want it to sort.

 

[Peter_SSs]

Your times may be Text not Numbers.
What does =ISNUMBER(B3) return if you put that in a blank cell and point it to one of those time cells (where I have B3)?

 

[eponcedeleon]

Sounds like a formatting issue, so lets look at your formatting first:
Select your data > Right Click > Format Cells > Select Numbers Tab at the top > Select Time in the Category List >Choose your ideal time format (e.g., "6:00 AM") > Apply settings
Next, sorting your data:
With your data selected go to the Data tab at the top > Click Sort A to Z or Z to A depending which way you want it to sort.

This is not working for me, I have tried it.

XL2BB is also not working, i can copy but have not been able to paste in mrexcel.com

 

[eponcedeleon]

Your times may be Text not Numbers.
What does =ISNUMBER(B3) return if you put that in a blank cell and point it to one of those time cells (where I have B3)?

I get false, so it is defenitely text.

 

[Peter_SSs]

I get false, so it is defenitely text.

Agreed. Try this

1. Check that the column format is General
2. Select the text time data (or whole column)
3. Data ribbon tab -> Text to Columns -> Delimited -> Next -> Next -> In the date drop-down select DMY or MDY (doesn't matter which) -> Finish
4. Now try sorting