How to have "Excel for Mac 2016" Auto-convert entry of yymmdd to yyyy-mm-dd

Question

I want to type, say, 20201008 or 201008 into a cell and as soon as I press  the enter or tab key have "Excel for Mac 2016" immediately convert and display either entry as 2020-10-08.


 I don't want to have another column set up that uses a formula to convert 20201008 or 201008 to 2020-10-08.


 I want Excel to do the conversion in the background, or as a formula for that cell, again, in the background, but that it works to allow me to type into that cell 20201008 or 201008 and as soon as I press the enter or tab key the cell displays 2020-10-08.


 Anyone got a way to do this?


 Thanks!

Answer 1

This is for column B only.

Put the following Event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim B As Range, Intersection As Range, r As Range
    Dim v As String, Lv As Long
    
    Set B = Range("B:B")
    Set Intersection = Intersect(B, Target)
    If Intersection Is Nothing Then Exit Sub
    For Each r In Intersection
        v = r.Text
        Lv = Len(v)
        If Lv = 8 Or Lv = 6 Then
            If IsNumeric(v) Then
                Application.EnableEvents = False
                    r.Clear
                    r.NumberFormat = "yyyy-mm-dd"
                    If Lv = 8 Then
                        r.Value = DateSerial(Left(v, 4), Mid(v, 5, 2), Right(v, 2))
                    Else
                        r.Value = DateSerial("20" & Left(v, 2), Mid(v, 3, 2), Right(v, 2))
                    End If
                Application.EnableEvents = True
            End If
        End If
    Next r
    
End Sub

The macro will convert your number input into a true Excel Date and format it correctly.