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]

The comma makes it an unrecognised date format, do a find/replace to remove the comma

 

[zalik22]

The comma makes it an unrecognised date format, do a find/replace to remove the comma

I manually removed the comma before running the code and it didn't work. It sorts by date then 1pm rows instead of the 7am rows.

 

[MARK858]

Can you post a sample of the original data using XL2BB please

 

[zalik22]

Does this work?

old format:

5/9/2024 6:35 AM

5/10/2024 3:19 PM

5/8/2024 1:16 PM

5/7/2024 3:57 PM

new format:

9/9/2024, 8:49 PM

9/9/2024, 8:22 PM

9/9/2024, 7:19 PM

9/9/2024, 7:03 PM

 

[MARK858]

They are all PM times which doesn't show the issue you stated (not sure yet whether you putting them in a table will work for testing or not)

 

[zalik22]

When I run the sort code, with the old data format (no code), it sorted by date and time correctly (AM rows then PM).

The new format with the comma sorts off the date, then time (ignoring AM/PM) so 1pm rows are first, then 10am, then 11am, then 12pm, then 2pm, etc.

They are all PM times which doesn't show the issue you stated (not sure yet whether you putting them in a table will work for testing or not)

 

[zalik22]

Here is another snippet of code:

'Sort by Local Start Time
Dim lastrow As Long

    With ActiveWorkbook.Worksheets("data")
    
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Sort.SortFields.Clear

        .Sort.SortFields.Add Key:=.Range("A1").Resize(lastrow), _
                             SortOn:=xlSortOnValues, _
                             Order:=xlAscending, _
                             DataOption:=xlSortNormal
        .Sort.SetRange .Range("A1:Z1").Resize(lastrow)
        .Sort.Header = xlYes
        .Sort.MatchCase = False
        .Sort.Orientation = xlTopToBottom
        .Sort.SortMethod = xlPinYin
        .Sort.Apply
    End With
'End Sort by Local Start Time

 

[MARK858]

Still need a sample of your data with the comma, before the sort and showing AM and PM (not re-typed and not an image, which is why I asked for you to use XL2BB), so we can test.

The code is pretty much irrelevant by what you have posted indicates it is your data layout that is the issue.

 

[Skyybot]

A small edit to your original code

'   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)

'   Gets rid of Commas in Active Column
 Columns(ActiveCell.Column).Replace ",",""

'   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