Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Aug 2009
    Location
    Canberra
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello

    I open a .CSV file which has dates in the Australian format (DD/MM/YYYY).

    When I save the file, Excel changes the dates to the US format (MM/DD/YYYY).

    Is there a way to prevent this?

    Thanks
    Marie-therese

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try formatting the cells as dd-mmm-yyyy or as yyyy/mm/dd before saving, this should remove any ambiguity about the date format.

  3. #3
    New Lounger
    Join Date
    Aug 2009
    Location
    Canberra
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans

    There is still a problem.

    The original CSV file had a few issues (I had to remove the N/A and one invalid date).
    I then formatted the cells as you described (using "DD/MM/YYYY") and saved the file as with a new name.

    When I reopen the Corrected CSV file all the dates have now changed. For example, the date on the first line was 10/7/2009, and is now displaying as 7/10/2009.

    How can I prevent this from happening?

    Thanks
    Marie-Therese

    Nb: I cannot attach the files as the upload facility won't allow this file type.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What is the system date format on the PC you're using? If it is set to US date format mm/dd/yyyy, that would explain the behavior that you describe.

    (You could zip a .csv file and attach the .zip file but I don't think that would help in this situation)

  5. #5
    New Lounger
    Join Date
    Aug 2009
    Location
    Canberra
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans

    The system settings on the computer are all set to Australian date/time. I have attached the CSV files in a zip.

    I was wondering if the CSV files don't save any of the date formats in them. Would that explain why Excel does all its own date interpretation when it opens the CSV file?

    Thank you so much for your help.
    Marie-Therese
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    These two CSV files appear to be identical on my PC, which is configured for UK English settings.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    A CSV file is a plain text file, it stores only the text, no formatting at all.

    It appears that your Excel uses US date format to display dates; that's strange if the system setting is Australian. I don't have an explanation for that...

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I have opened them also in Excel on a computer that also uses Australian settings, and both files were identical, and both showed dates the correct way: e.g. 19/03/2007.
    Regards
    John



  9. #9
    New Lounger
    Join Date
    Aug 2009
    Location
    Canberra
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    When I open the file using "File/Open" from Excel, the second date displays as 7/10/2009.

    However, when I double click on the CSV file, the second date displays as 10/7/2009.

    It would be good the best way to open the file so that the dates aren't changed by Excel.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I still find this very strange - on my PC, it doesn't make a difference whether I open your .csv files by double-clicking in Windows Explorer or by opening them from within Excel. It's as if there is a US system setting lurking somewhere...

  11. #11
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Marie

    Shot in the dark here, I have just undertaken a project where I had a team ensure all the PCs in the call centre were up to the most current Service Pack and Hot Fixes. There was a problem when a set of hot fixes failed and caused the PCs display the dates in mm/dd/yyyy format. Ensuring the hot fixes were implemented solved the problem, can you test that your PC is fully service packed and hot fixed this may remove the problem
    Jerry

  12. #12
    New Lounger
    Join Date
    Aug 2009
    Location
    Canberra
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you everyone. I had my PC reimaged and now it works perfectly.

Posting Permissions

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