My suggestion is a bit left-field. Whilst I applaud your efforts to try
and account for the inconsistencies of file formats arriving, surely you
can exert some influence to ensure that files arrive in the correct
format (the format that you specify)? We have had similar issues at
work, and a bit of consultation, or stamping of feet, solved the
problem.

Alternatively, a proper date  is actually a numeric value of Julian date.
So, why don't you look if ISNUMERIC, and loop through the data that way,
correcting only non-numeric entries.