Auto format string to date

Auto format string to date     

Private Sub Worksheet_Change(ByVal Target As Range)

‘ Auto format cell from date string

Dim StrVal As String
Dim dDate As Date

     If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range(“A1:A65536”)) Is Nothing Then Exit Sub
     
     With Target
         StrVal = Format(.Text, “000000”)
          If IsNumeric(StrVal) And Len(StrVal) = 6 Then
            Application.EnableEvents = False
            
            If Application.International(xlDateOrder) = 1 Then ‘dd/mm/yy
                dDate = DateValue(Left(StrVal, 2) & “/” & Mid(StrVal, 3, 2) & “/” & Right(StrVal, 2))
            Else ‘mm/dd/yy
                dDate = DateValue(Mid(StrVal, 3, 2) & “/” & Left(StrVal, 2) & “/” & Right(StrVal, 2))
            End If
            
            .NumberFormat = “dd/mm/yyyy”
            .Value = CDate(DateSerial(Year(dDate), Month(dDate), Day(dDate)))
           
          End If
    End With
           
     Application.EnableEvents = True

End Sub

Leave a Reply