This user-defined function (UDF) converts numbers in format YYYYMMDD to actual Excel dates

convert_yyyymmdd_to_date.vb

Option Explicit
Public Function CONVERT_YYYYMMDD_TO_DATE(rng As Range) As Date
    Dim strText As String

    'First we get the raw text from the Range (i.e. the Cell)
    strText = rng.Text

    'Then we can slice and dice the number, feeding it all into DateSerial
    CONVERT_YYYYMMDD_TO_DATE = DateSerial(Left(strText, 4), _
                                          Mid(strText, 5, 2), _
                                          Right(strText, 2))
End Function