Date and Time Not Sorting

[zalik22]

Hi,

I used to have time/date data coming in with m/d/yyyy h:mm AM/PM format and now it has a comma in the middle of it (m/d/yyyy, h:mm AM/PM) and after running my code, it is not sorting properly. Any idea what's wrong?

'   Find which column "Local Start Time" appears in
    Rows("1:1").Find(What:="Local Start Time", After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    sortAdd = ActiveCell.Address(0, 0)
  
'   Convert entries in Date column to valid dates
    Columns(ActiveCell.Column).TextToColumns Destination:=Range(sortAdd), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True
          
'   Format columns
    Columns(ActiveCell.Column).NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"

'   Set sort range by using current region
    Range("A1").CurrentRegion.Sort _
        Key1:=Range(sortAdd), Order1:=xlAscending, Header:=xlYes

 

[MARK858]

A small edit to your original code

You mean what I suggested in post 2, did you test with what the OP posted in post 5 (which was originally in table tags within code tags), it also needs a space and a unicode character removed?

 

[zalik22]

You mean what I suggested in post 2, did you test with what the OP posted in post 5 (which was originally in table tags within code tags), it also needs a space and a unicode character removed?

Removing the comma did not work, I cannot install the xl2bb add on, might be limited permissions on my laptop.

 

[MARK858]

It won't work if what you posted in post 5 is correct (not sure if your method of displaying is giving the correct info).
Try selecting the column and run the below and see if it changes the data

Sub Fixit()
    With Selection
        .Replace ",", "", xlPart
        .Replace ChrW(8239) & "PM", " PM", xlPart
    End With
End Sub

 

[zalik22]

It won't work if what you posted in post 5 is correct (not sure if your method of displaying is giving the correct info).
Try selecting the column and run the below and see if it changes the data

Sub Fixit()
    With Selection
        .Replace ",", "", xlPart
        .Replace ChrW(8239) & "PM", " PM", xlPart
    End With
End Sub

This removed the PM and sorted them on top. The AM dates and times were sorted below the dates with no AM/PM. The time also changed to 13:00 for 1:00PM.

 

[MARK858]

The PM will either be there or not depending on how your cells are formatted (that is down to you to deal with)

The AM dates and times were sorted below the dates with no AM/PM

I never put anything for the AM times as the code was to test if it corrected the data you posted in the thread , which didn't have the AM (by the way nothing should have sorted if you done as I instructed and ran the code as I posted it, you have obviously added it to your code before testing... just for reference this is not a good idea when someone asks you to test something, as it complicates the results).

You need to add a separate line to cover the AM cells as per the below and you need to add code for the formatting if you require it, as they are now real dates and times that default to your default format and not text.

Sub Fixit()
    With Selection
        .Replace ",", "", xlPart
        .Replace ChrW(8239) & "PM", " PM", xlPart
        .Replace ChrW(8239) & "AM", " AM", xlPart
    End With
End Sub

 

[MARK858]

Please note I have edited the previous post as I hadn't changed the 2nd PM to AM

 

[zalik22]

Hi, I caught it. Seems to sort, I just need to work on getting the AM and PM to show up now. It's in the cell but not being displayed.

Please note I have edited the previous post as I hadn't changed the 2nd PM to AM

 

[MARK858]

It isn't in the cell. It needs formatting the same as you would any other date cell, just turn on the macro recorder and format it manually and you'll get the code.

 

[zalik22]

It isn't in the cell. It needs formatting the same as you would any other date cell, just turn on the macro recorder and format it manually and you'll get the code.

Done, thanks for your help and patience with this! I marked the solution too.

 

[MARK858]

You're welcome, happy you got it "sorted"

P.S. as one of the other MVP's pointed out to me you can probably get away with the just the line below to cover both the AM and PM

.Replace ChrW(8239), " ", xlPart

Edited because I still can't tell the difference between your and you're