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



## kellman (Jan 2, 2023)

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.xlsmA1Actual Appt Start212/9/2022 12:00 PM312/9/2022 4:00 PM412/9/2022 8:00 AMSheet1

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 (Jan 2, 2023)

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
Book8ABC1Actual Appt StartFormat General212/09/2022 8:00 am44816.33333312/09/2022 12:00 pm44816.5412/09/2022 4:00 pm44816.666675Sheet1


----------



## Micron (Jan 2, 2023)

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 (Jan 2, 2023)

etaf said:


> check the cells are still formatted as a date and not text
> 
> change the format to general
> 
> ...


I have tried your solution and no change.

Address stripper test vba with data.xlsmABC1Actual Appt StartFormat as General212/9/2022 12:00 PM12/9/2022 12:00 PM312/9/2022 12:00 PM12/9/2022 12:00 PM412/9/2022 12:00 PM12/9/2022 12:00 PM512/9/2022 4:00 PM12/9/2022 4:00 PM612/9/2022 4:00 PM12/9/2022 4:00 PM712/9/2022 8:00 AM12/9/2022 8:00 AM812/9/2022 8:00 AM12/9/2022 8:00 AM912/9/2022 8:00 AM12/9/2022 8:00 AM1012/9/2022 8:00 AM12/9/2022 8:00 AMSheet1


----------



## kellman (Jan 2, 2023)

Micron said:


> 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 (Jan 2, 2023)

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 (Jan 2, 2023)

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 (Jan 2, 2023)

A2 + 1 = #VALUE!

VALUE!​


----------



## kellman (Jan 2, 2023)

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 (Jan 2, 2023)

kellman said:


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


----------



## kellman (Jan 2, 2023)

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.xlsmA1Actual Appt Start212/9/2022 12:00 PM312/9/2022 4:00 PM412/9/2022 8:00 AMSheet1

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.


----------



## Micron (Jan 2, 2023)

Post 7 worked for me with text.


----------



## kellman (Jan 2, 2023)

I am completely baffled.  Again everything was working earlier today, no software or update changes made.
I was able to sort the Date column without any special formatting previously.
I even installed an older copy of Office 2007 and have the same issue.

I have uploaded a copy here, if you don't mind taking a look:
Date Sorting Example for Mr Excel.xlsx


----------



## kellman (Jan 2, 2023)

Okay I think that I'm onto something here.
Running Windows 10 current version / build.

I changed Windows 10 Region Date & Time Data Format to match the short date format Date I'm using in my excel project to DD-MM-YY and now I can sort the date column normally!

This time I received the "Sort Warning" dialogue box mentioned earlier, and after clicking the "OK" button, all worked properly as it had earlier this morning.

Struggling to wrap my head around how changing Windows 10 Date Time Data settings made this work, as I never changed anything in the first place.

I hope this helps someone else that might experience this bug.


----------



## Peter_SSs (Jan 2, 2023)

Glad you got it sorted. Thanks for letting us know.


----------

