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

    I have an XL spreadsheet containing a column with the date 31-8-2009.

    The date column is formatted as mmm-yyyy, so it displays as Aug-2009.

    If I save the spreadsheet to a CSV file, the date value becomes 1-8-2009.

    I was wondering if the date format affects the values stored in the CSV file?

    I have attached a sample the Excel spreadsheet with a date.

    Thank you!
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Did you try using other date formats before exporting to csv?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The CSV does not store date information, only text information. The text information it stores is dependent on the display. If you look at the CSV created in a text reader, you will see that "Aug-2009" is stored. Thus the CSV does not have the day of the month stored.

    When you import in Excel, it reads the "Aug-2009" and converts the text-date (text that looks like a date) to a serial-date. Without the day of the month indication, excel defaults to the first day of the month...

    You should display as "mmm d, yyyy" before converting to CSV so that when it is saved it contains the day of the month and it can be imported with this information.

    [Note: I would avoid the dd/mm/yyyy or mm/dd/yyyy as excel is sometimes US-centric and it could convert some incorrectly and fail to convert others. Using mmm for the month avoids any ambiguity for excel...]


    Steve

Posting Permissions

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