Australian dates converting to American dates (Excel 2003/Windows XP)

Topic

New Reply

Please help. I have recently received a new intel core 2 duo with windows XP and Excel 2003 – appears on older pcs we have as well. We have also recently has TRIM installed. We receive 200+ data files (containing a total of 200,000+ rows of data) at the end of each financial year in .csv format. Recently whenever I save the .csv file (from within Excel) my australian date format (d/mm/yyy) converts to american date format (m/dd/yyyy). The .csv files have 24+ columns and 5 of these are date fields (interspersed). I have looked at my regional settings in the Control Panel and my local format options in Excel and they are all set to Australian date formats. It appears that even the Auto Save function is changing the date format. I have attached a sample of the type of file we receive.
I have created a formula to convert the dates back which works well until I save the file and have to do it over and over again.
I am quite desperate. If anyone can offer any advice or provide any sort solution I would be exceedingly grateful.

Reply | Quote

Replies

Do you mean TRIM from TOWER Software?

Reply | Quote

Yes TRIM from TOWER is our new EDRMS

Reply | Quote

If you are absolutely sure that all system settings are Australian, I would suspect TRIM to interfere. I don’t know if there are internal settings in TRIM to specify a date format.

Reply | Quote

Thanks Hans – I will persist in my efforts to get our TRIM administrators to take responsibility.

Reply | Quote

Suzie,

There is a known ‘bug’ in Excel 2003 when importing dates in the dd/mm/yyyy format when importing from a CSV file using VBA. See Knowledgebase article 911750 – “The format of the dates is incorrect when you use a VBA macro to convert a CSV text file in Excel 2003” – for details.

I am in Australia and encountered a problem similar to yours. I got the Hotfix mentioned in the KB article but decided not to use it because it was a lot older than my version of Excel (due to updates, Security patches, etc.) and was worried that applying it would cause more trouble than it was worth.

I opted to use the code modification (add the parameter to your VBA macro) as specified in the KB article, and everything worked fine after that.

HTH

Regards,

John

Reply | Quote