0

I have a data sheet which looks like this, with General Format:

Date
01.02.2020
02.01.2020
01.02.2021
02.01.2021

I need to sort it chronologically and keep the format dd.mm.yyyy . It should work on all systems like German, US etc...

Using this code messes up the sorting (sorting by dd and not chronologically):

Set ws = ActiveWorkbook.Worksheets(1)
ws.Range("A2", ws.Range("A2").End(xlDown)).NumberFormat = "dd.mm.yyyy"

    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A1", Range("A1").End(xlDown))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Any ideas, how I can achieve that? Thanks a lot!

Peer Breier
  • 361
  • 2
  • 13
  • 1
    Maybe try sorting by the number value rather than date formatted value? This way the dates *must* be in chonological order and can be displayed on the sheet in the dd/mm/yyyy format? – Samuel Everson Apr 29 '21 at 13:04
  • 2
    Dates are just numbers. If you apply General formatting and it still looks like a date, then you don't actually have a date, you have text-that-looks-like-a-date. – BigBen Apr 29 '21 at 13:05
  • All true, but when I import it to another file, there I have date format. Would you convert all dates to text then? – Peer Breier Apr 29 '21 at 13:14
  • Are you wanting the dates to be text rather than an *actual* date value for any particular reason? – Samuel Everson Apr 29 '21 at 13:18
  • No, I want it to be dates. I am struggling because I cannot convert it with .NumberFormat and sort it correctly then. – Peer Breier Apr 29 '21 at 13:20
  • 2
    Format and the underlying value are separate. `.NumberFormat = "dd.mm.yyyy"` doesn't convert the underlying value, which is still text. – BigBen Apr 29 '21 at 13:20
  • There must be the solution. Can I convert the General format to the underlying value, sort and then do .NumberFormat? – Peer Breier Apr 29 '21 at 13:22
  • 2
    [Many options](https://www.google.com/search?&q=excel+convert+text+to+date). [More options](https://www.google.com/search?q=excel+convert+text+to+date+site:stackoverflow.com) – BigBen Apr 29 '21 at 13:24
  • I'd suggest you sort in in Excel (not VBA), If that works then record a macro and adjust the resulting VBA to your needs. – StureS Apr 29 '21 at 13:31
  • @StureS Unfortunately, that will only work on my system, but not on a system with different location settings – Peer Breier Apr 29 '21 at 13:34
  • 2
    A date in Excel is just a number where the integer part is days and the fractional part is hours, minutes and seconds. That is universal regardless of location. If you cannot sort a date successfully then there must be something fundamentally wrong with your custom sorting. – StureS Apr 29 '21 at 13:56

1 Answers1

0

I finally found a nice and efficient solution, which I got from @Siddharth Rout here: https://stackoverflow.com/questions/67315273/can-cdate-be-used-on-a-range-without-a-for-l[oop][1]

    Dim rng As Range
    Dim sAddr As String
    Dim lRow As Long
    Dim ws As Worksheet

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        Set rng = .Range("A:A" & lRow)
        sAddr = rng.Address

        rng = Evaluate("index(DATE(RIGHT(" & sAddr & _
                       ",4),MID(" & sAddr & ",4,2),LEFT(" & _
                       sAddr & ",2)),)")
        End With
        
    With ws.Sort
        .SortFields.Add Key:=ws.Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange rng
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    rng.NumberFormat = "dd.mm.yyyy;@"
Peer Breier
  • 361
  • 2
  • 13