Excel Convert the correct Date type

Are you willing to use a “helper column”?  If you’re pasting the “dd mmm hhmm” data into Column P (let’s assume starting at P2 and going down), put

=TEXT(P2,"dd mmm yyyy")

into cell Q2 and drag/fill down.  This basically reverses the interpretation that Excel did on the input string, reconstituting what got parsed as the day, month, and year.  Strings that didn’t get interpreted as dates (because the four-digit number is less than 1900) are simply passed through unchanged.

This will cause blank cells in Column P to be translated into 00 Jan 1900.  If you don’t like that, use

=IF(P2<>"", TEXT(P2,"dd mmm yyyy"), "")

instead.

This will represent months with the typical initial capital (Mmm, e.g., “Nov”).  If you want it all caps, use

=IF(P2<>"", UPPER(TEXT(P2,"dd mmm yyyy")), "")

Select the cells and run:

Sub DateFixer()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
        For Each r In Selection
            v = r.Text
            r.Clear
            r.NumberFormat = "dd/mm/yyyy hh:mm:ss"
            r.Value = DateSerial(Mid(v, 7, 4), Mid(v, 4, 2), Left(v, 2)) + TimeSerial(Mid(v, 12, 2), Mid(v, 15, 2), Right(v, 2))
        Next r
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub