Here's a simple macro that can be run after pasting data from SSMS. It's easiest if you copy it to your PERSONAL.XLSB file and add a button to the Quick Access Toolbar or a new custom group/tab in the ribbon. Run the macro immediately after pasting while the data is still selected. It can also be run if a single cell is selected within the data - it will automatically select the current area before running (same as ctrl-a). To run the macro on only a subset of data, select the desired subset before running. It can handle the data including or excluding headers, but assumes there at at least 2 rows in the current area.
It efficiently tests each column to see if the first non-NULL value looks to be a date/time value in the strange format. If it is, it sets the entire column to the default system date/time format, even if your date format is 'd/m/y'.
Sub FixSSMSDateFormats()
'Intended for copied data from SSMS and handles headers included
'For selection or current area, checks each column...
' If the first non-NULL value is in strange time format, then change entire column to system date/time format
Dim values As Variant, r As Long, c As Long
If Selection.Count = 1 Then Selection.CurrentRegion.Select
values = Selection.Value
For c = 1 To UBound(values, 2)
For r = 2 To UBound(values, 1)
If TypeName(values(r, c)) = "Double" Then
If values(r, c) > 1 And Selection(r, c).NumberFormat = "mm:ss.0" Then
Selection.Columns(c).NumberFormat = "m/d/yyyy h:mm"
End If
Exit For
ElseIf values(r, c) <> "NULL" Then
Exit For
End If
Next
Next
End Sub