How to standardize date format by VBA-CodePudding

There is column A and B with different date format :

20200714
44043
2020/09/01
2021/1/4

is there any VBA to standardize the date format as the same format?

2020/7/14
2020/7/31
2020/9/1

2021/1/4

 Sub text()
  Dim lastRow, l As Long
  lastRow = Range("a65536").End(xlUp).Row

  For l = 2 To lastRow
  Range("A" & l).Select
  Selection.NumberFormatLocal = "yyyy/m/d"
  Next
End Sub

after run code, some cell show as ####, some looks good as yyyy/m/d.

CodePudding user response：

Please, try the next function:

Function DateConv(strVal) As Date
   If IsDate(strVal) Then
        DateConv = strVal
   ElseIf IsNumeric(strVal) And Len(strVal) = 5 Then
        DateConv = CDate(CLng(strVal))
   ElseIf IsNumeric(strVal) And Len(strVal) = 8 Then
        DateConv = DateSerial(CLng(left(strVal, 4)), CLng(Mid(strVal, 5, 2)), CLng(Right(strVal, 2)))
    ElseIf Len(strVal) = 8 Then
       Dim arrD: arrD = Split(strVal, "/")
       DateConv = DateSerial(CLng(arrD(0)), CLng(arrD(1)), CLng(arrD(2)))
   End If
End Function

It can be used to convert a column (A:A as example) in the next way:

Sub makeDate()
   Dim sh As Worksheet, lastR As Long, arr, arrD, i As Long
   
   Set sh = ActiveSheet
   lastR = Range("A" & sh.rows.count).End(xlUp).row
   
   arr = sh.Range("A2:A" & lastR).value
   ReDim arrD(1 To UBound(arr), 1 To 1)
   For i = 1 To UBound(arr)
        arrD(i, 1) = DateConv(arr(i, 1))
   Next i
   
   With sh.Range("A2").Resize(UBound(arrD), 1)
        .NumberFormat = "yyyy/mm/dd"
        .value = arrD
    End With
End Sub

Of course, the range to be converted must exist in column "A:A"...

There may be problems only if the format date (brought from somewhere...) does not have a similar format. I mean "yyyy/dd/mm" instead of "yyyy/mm/yy".