Convertir un nombre en date

=DATE(STXT(A1;1;4);STXT(A1;5;2);STXT(A1;7;2))

=DATE(ANNEE(GAUCHE(B7;4));MOIS(STXT(B7;5;2));JOUR(DROITE(B7;2)))

Sub NombreVersDate()
Dim P As Range, tablo, i&, x$
With ActiveSheet
    If .FilterMode Then .ShowAllData 'si la feuille est filtrée
    Set P = .UsedRange.Columns(1) '1ère colonne, à adapter
End With
tablo = P.Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
Application.ScreenUpdating = False
P.NumberFormat = "dd/mm/yyyy" 'format Date
For i = 1 To UBound(tablo)
    x = tablo(i, 1)
    If x Like "########" Then
        x = Right(x, 2) & "/" & Mid(x, 5, 2) & "/" & Left(x, 4)
        If IsDate(x) Then tablo(i, 1) = CDate(x) Else P(i).NumberFormat = "General"
    Else
        P(i).NumberFormat = "General" 'format Standard
    End If
Next
P = tablo 'restitution
End Sub

Sub DateVersNombre()
Dim P As Range, tablo, i&, x
With ActiveSheet
    If .FilterMode Then .ShowAllData 'si la feuille est filtrée
    Set P = .UsedRange.Columns(1) '1ère colonne, à adapter
End With
tablo = P.Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
For i = 1 To UBound(tablo)
    x = tablo(i, 1)
    If IsDate(x) Then tablo(i, 1) = Format(x, "yyyymmdd")
Next
Application.ScreenUpdating = False
P.NumberFormat = "General" 'format Standard
P = tablo 'restitution
End Sub

Sub Conversion()
If IsError(Application.Caller) Then Exit Sub
Dim P As Range, tablo, o As Object, i&, x
With ActiveSheet
    If .FilterMode Then .ShowAllData 'si la feuille est filtrée
    Set P = .UsedRange.Columns(1) '1ère colonne, à adapter
    tablo = P.Resize(, 2) 'matrice, plus rapide, au moins 2 éléments
    Set o = .DrawingObjects(Application.Caller)
End With
Application.ScreenUpdating = False
If o.Text Like "Nombre*" Then
    o.Text = "Date vers Nombre"
    P.NumberFormat = "dd/mm/yyyy" 'format Date
    For i = 1 To UBound(tablo)
        x = tablo(i, 1)
        If x Like "########" Then
            x = Right(x, 2) & "/" & Mid(x, 5, 2) & "/" & Left(x, 4)
            If IsDate(x) Then tablo(i, 1) = CDate(x) Else P(i).NumberFormat = "General"
        Else
            P(i).NumberFormat = "General" 'format Standard
        End If
    Next
Else
    o.Text = "Nombre vers Date"
    For i = 1 To UBound(tablo)
        x = tablo(i, 1)
        If IsDate(x) Then tablo(i, 1) = Format(x, "yyyymmdd")
    Next
    P.NumberFormat = "General" 'format Standard
End If
P = tablo 'restitution
End Sub