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?


  • Did you try to format this cell(s) as date cell with format which you want to have?
    – mariaczi
    Oct 8, 2020 at 15:21

1 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.NumberFormat = "yyyy-mm-dd"
                    If Lv = 8 Then
                        r.Value = DateSerial(Left(v, 4), Mid(v, 5, 2), Right(v, 2))
                        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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .