The DateTime represented by the string is not supported in calendar

Hi,

I had a Excel spreadsheet with a column with date format “dd/MM/yyyy”. I have ran the below VB script to change it to “yyyy/MM/dd” as I need to sort it by that column and I was unable to make it work with the original format.
Now, I am getting the error “The DateTime represented by the string is not supported in calendar System.Globalization.GregorianCalendar”

VB script

Sub ChangeDate()
Dim r As Range, temp As String
For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
r.Value = Evaluate(“if(len(” & r.Address & “),clean(trim(” & r.Address & “)),” & r.Address & “)”)
With CreateObject(“VBScript.RegExp”)
.Pattern = “^(\d{2})/(\d{2})/(\d{4})( *\d{2}(:\d{2}){2})?$”

    If .test(r.Text) Then
            temp = r.Text
            r.Value = DateSerial(.Replace(r.Value, "$3"), _
            .Replace(r.Value, "$2"), .Replace(r.Value, "$1"))
            r.NumberFormat = "yyyy-mm-dd"
    End If
  End With
    Next   

End Sub

Any help would be highly appreciated.

Dim myDate as date
myDate = DateTime.parseExact(yourDateString, "dd/MM/yyyy")
Dim myNewString as string
myNewString = myDate.ToSTring("yyyy/MM/dd")