Opening a csv file with US date format on a Australian PC

[Troy Lea]

Having problems with formating dates correctly in Excel when opened from a
CSV file. The data coming in is in the US format "MM/DD/YYYY HH:MM AM/PM".
IE: "6/17/1985 12:00:00 AM".

When the CSV file is opened in Excel you are unable to change the formatting
to set it to Australian date format "DD/MM/YYYY HH:MM AM/PM"; the data stays
the same as "6/17/1985 12:00:00 AM".

When the MM and DD digits are 12 and less it recognises them as valid dates.
IE "11/1/1995 12:00:00 AM" is automatically formated as "11/01/1995 0:00".
However this is only happening because both MM and DD are equal to or less
than 12.

My question is how do I import the data into Excel telling it that the
format of the data coming in is "MM/DD/YYYY HH:MM AM/PM" and I want it
formatted as "DD/MM/YYYY HH:MM AM/PM"? Unfortunately when we receive the data
from the database it includes the time in the date field, we have no control
over this.

 

[JulieD]

Hi Troy

if you change the file extension before importing to .txt you will then get
the import wizard when you open the file in Excel... one of the screens
allows you to specify the date format of the imported data - you might like
to give this a go and see if it helps

Cheers
JulieD
Perth, Western Australia

 

[Troy Lea]

We've tried that but unfortunately because there is time included in the
field this method doesn't work.