Forum Discussion
How to Change Date/Time Format for whole column
You started with the problem of dates as text in US format.
The ideal solution is to import the text using PowerQuery which can be used to transform the data before it is loaded to the sheet.
Once such a file has been loaded to the workbook, however, you have two distinct problems. The first is that every value that Excel has converted to a date will be wrong. Excel will show the 4th March when the intention was 3rd April. The second is that your non-US version of Excel will have given up when asked to name the 13th month and left the US formatted date as text.
The solution is to test the date field to see whether you have text or a number and to pick the day, month and year using the appropriate formulas. The DATE function creates an appropriate datevalue in either case.