Custom Sort Column by Time AM PM not working or Bug?

[kellman]

I'm trying to sort the Column by it's date & time AM/PM in order of earlier to oldest.

This was sorting correctly earlier, but now refuses to sort correctly.

Microsoft® Excel® for Microsoft 365 MSO (Version 2211 Build 16.0.15831.20098) 64-bit

It should list the time sorted as this:

8:00 AM
12:00 PM
4:00 PM

Instead I get the following weird order.

Dates with AM PM.xlsm
	A
1	Actual Appt Start
2	12/9/2022 12:00 PM
3	12/9/2022 4:00 PM
4	12/9/2022 8:00 AM
Sheet1

Note: I've tried with Cells formatted as Text and also by Date, no change
Tried Sort A-Z and Z-A and no change.
Rebooted computer with no change.

 

[etaf]

check the cells are still formatted as a date and not text

change the format to general

they should all change to a number

working for me

Book8
	A	B	C
1	Actual Appt Start		Format General
2	12/09/2022 8:00 am		44816.33333
3	12/09/2022 12:00 pm		44816.5
4	12/09/2022 4:00 pm		44816.66667
5
Sheet1

 

[Micron]

It appears to be sorting as text. Have seen this before. Change to General, then custom as h:mm:ss AM/PM and should work after that.

 

[kellman]

check the cells are still formatted as a date and not text

change the format to general

they should all change to a number

working for me

Book8
	A	B	C
1	Actual Appt Start		Format General
2	12/09/2022 8:00 am		44816.33333
3	12/09/2022 12:00 pm		44816.5
4	12/09/2022 4:00 pm		44816.66667
5
Sheet1

I have tried your solution and no change.

Address stripper test vba with data.xlsm
	A	B	C
1	Actual Appt Start		Format as General
2	12/9/2022 12:00 PM		12/9/2022 12:00 PM
3	12/9/2022 12:00 PM		12/9/2022 12:00 PM
4	12/9/2022 12:00 PM		12/9/2022 12:00 PM
5	12/9/2022 4:00 PM		12/9/2022 4:00 PM
6	12/9/2022 4:00 PM		12/9/2022 4:00 PM
7	12/9/2022 8:00 AM		12/9/2022 8:00 AM
8	12/9/2022 8:00 AM		12/9/2022 8:00 AM
9	12/9/2022 8:00 AM		12/9/2022 8:00 AM
10	12/9/2022 8:00 AM		12/9/2022 8:00 AM
Sheet1

 

[kellman]

It appears to be sorting as text. Have seen this before. Change to General, then custom as h:mm:ss AM/PM and should work after that.

I have tried your solution and no change.

 

[etaf]

they are TEXT and not dates - time
you should have seen numbers like i posted

so they will NOT sort by time

in a new cell - Add one to the value

A2+1
should add a day
as they are text - will get a value error

 

[Peter_SSs]

If the underlying data is text, which it appears to be (left alignment), then changing the cell formatting will not change the values into numbers.

After you click OK to the Sort dialog shown in the post 1 image, don't you get an option like this?

Perhaps best anyway to convert them to actual date/time.
Select the data - Text to columns - Delimited - Next - Next - Choose Date and select whether you have DMY or MDY order for your dates - Finish

 

[kellman]

A2 + 1 = #VALUE!

VALUE!​

 

[kellman]

No Sort Warning dialog box after clicking OK.

Also I tried your second suggestion, also found in a Mr. Excel youtube video here, with no luck.
Excel date formatting NOT working? Dates not sorting in Excel?

I'm starting to think this must be a software bug. Maybe I'll have to uninstall and reinstall MS Office 365.

 

[Micron]

Maybe I'll have to uninstall and reinstall MS Office 365.

I'll put money on that not helping. I suppose xl2bb formats your data because what I posted worked for me when I copied it. If you want to upload a wb copy somewhere, I or anyone who's ok with downloading it will take a look. You could remove everything else but the data you posted. After looking at your pic, I agree. Left justified means text unless you purposefully did that.
Also, select one of those cells and look for a single quote at the far left in the formula bar.