Solved: sort dates

[stefan0901]

I have a problem with sorting dates. I tried to write a code in vba to sort data in a normal ascending way, but I always get something like this:

01/01/2022
01/01/2025
01/01/2025
01/02/2011
01/02/2012
01/03/2012
01/03/2012
01/04/2011

I guess excell thinks that the first part represents the years, the second the months and the third the days. I tried like thousands of ways to solve this, but it doesn't work. It seems so easy... Does anybody propose a code ?

[mikerickson]

That sort acts as if those are text. Data in the form of Excel serial dates sorts properly.

Another alternative would be to put the strings in yyyy/mm/dd format.

[stefan0901]

Indeed, excel threat them as text and I know why. The data is downloaded drom the internet and there are dates, not all of them, which have an apostrophe just before the value of the actual date e.g. '01/01/2022. Now, I can't delete these apostophes in VBA, since they are not recognized. e.g. " ?Range ("A1").value " would give "01/01/2022". However, I am able to delete them manually in excel (one for one), but I need a VBA code. Then, I am also able to delete them by using the conversion function, but the code in vba doesn't work as well. This is all weird!

[GTO]

Hi Stefan,

Have you tried flipping the range's vals into an array and back?

Option Explicit

Sub StripPrefix()
Dim rng As Range
Dim ary

Set rng = Sheet1.Range("A1:A" & Sheet1.Cells(Rows.Count, 1).End(xlUp).Row)
ary = rng.Value
With rng
.ClearContents
.Value = ary
End With
End Sub


Hope that helps,

Mark

[mdmackillop]

You could lose the apostophe by getting the rightmost characters
=DATEVALUE(RIGHT(G20,10))

[mikerickson]

The easiest way to convert those into Excel dates would be to do TextToColumns on them. Tab delimited. And the output to be in General format.