Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Do you mean TRIM from TOWER Software?

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Yes TRIM from TOWER is our new EDRMS

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  5. #5
    New Lounger
    Join Date
    Jun 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  6. #6
    New Lounger
    Join Date
    Jul 2007
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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 <Local:=True> parameter to your VBA macro) as specified in the KB article, and everything worked fine after that.

    HTH

    Regards,

    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •