date format

[Dummy Excel]

Hi All,
I have a report and the date format is in US format and I require AU format although it actually comes through in 2 different ways:

5/12/2021  22:00:08
5/13/2021 7:01:00 AM

No idea why some data has AM/PM and the other doesn't

is there a formula or VBA that can;
change the format into AU
separate date and time into separate columns


thanks
Samuel

 

[Alex Blakenburg]

Can you update your profile to show what version of Excel you are running ?
How are you getting the data, excel, csv, text file ?

 

[Dummy Excel]

sorry its a .csv

 

[Alex Blakenburg]

I think your best options are:

• Use Power Query to import it and choose using locale on the date column (setting it to US)
• Change the csv extension to txt so that it doesn't bypass the import wizard and in then select that column > set it to date > select MDY
  (ignore the preview screen in the below, it is just what I had open at the time)
  
  
  

 

[RoryA]

This appears to basically be the same question as this one, so I'm locking this.