Need Help Handling Different Date Time Formats Imported from Multiple Excel Workbooks

Thanks BA_Pete 
I tried both Locale > US and UK and no joy - still those errors unfortunately:

EDIT: I think part of the issue here is that the date format is from two different locales - so when a colleague pulls the NEW exports from his system, he gets it in US format but also with the AM and PM , and for the historical files, they were pulled in the UK so UK format of dd/mm and without the AM PM.