Format a date that following format M/D/YYYY-CodePudding

I got the following format for the date 1/1/2022 (MM/DD/YYYY) and I need to transform it into 01.01.2022 (DD.MM.YYYY)

I'm doing this transformation inside an array()

For iDateArrayRow = 1 To UBound(iDateArray, 1)
    iDateArray(iDateArrayRow, 1) = Split(iDateArray(iDateArrayRow, 1), "/")
    If iDateArray(iDateArrayRow, 1)(0) < 10 Then
        iDateArray(iDateArrayRow, 1)(0) = "0" & iDateArray(iDateArrayRow, 1)(0)
    Else
        iDateArray(iDateArrayRow, 1)(0) = iDateArray(iDateArrayRow, 1)(0)
    End If

           
    If iDateArray(iDateArrayRow, 1)(1) < 10 Then
        iDateArray(iDateArrayRow, 1)(1) = "0" & iDateArray(iDateArrayRow, 1)(1)
    Else
        iDateArray(iDateArrayRow, 1)(1) = iDateArray(iDateArrayRow, 1)(1)
    End If

    iDateArray(iDateArrayRow, 1) =
Next iDateArrayRow

CodePudding user response：

If you really want to deal with strings (that look like a date), use the following function. Note that there are no dates involved, only strings.

Function ConvertMe1(d As String) As String
    Dim tokens() As String
    tokens = Split(d, "/")
    If UBound(tokens) <> 2 Then
        ConvertMe = d
    Else
        ConvertMe = tokens(1) & "." & tokens(0) & "." & tokens(2)
    End If
End Function

If you want to convert your string into a Date:

Function ConvertMe2(d As String) As Date
    Dim tokens() As String
    tokens = Split(d, "/")
    If UBound(tokens) <> 2 Then Exit Function
    
    ConvertMe2 = DateSerial(Val(tokens(2)), Val(tokens(1)), Val(tokens(0)))
End Function

Note that a Date is a Number (Double). A Date can be formatted in many different ways in Excel (without changing it's value). A string cannot be formatted in Excel.