Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Aug 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Dates (2002, 2003)

    Given a CSV file, Test.csv containing dates in the format DD/MM/YYYY such as "04/01/2006","08/02/2006","13/01/2006" etc etc
    When this file is opened by Excel the dates with days 12 or less are incorrectly interpreted as MM/DD/YYYY while those with days greater than 12 will be correctly interpreted as DD/MM/YYYY. Does anyone know of a patch, fix, workaround?

  2. #2
    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

    Re: Excel Dates (2002, 2003)

    What date format do you have for your regional settings in windows control panel?

    Steve

  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

    Re: Excel Dates (2002, 2003)

    I can not replicate the problem in XL XP. Even importing as US does not give what you indicate.

    If I change the regional settings to US, it imports them assuming mm/dd/yyyy. Anything that the frist 2 digits is >12 is imported as text and no conversion is done.

    If I change the regional settings to European, it imports them all correctly assuming dd/mm/yyyy.

    VB, I know assumes dates are US formatted no matter what the regional settings, but excel seems to understand them fine...

    How do you know they are interpreted incorrectly? I checked the import by chaning the format of the imported cells to "mmm d, yyyy" which displays them unambiguously.
    Steve

  4. #4
    New Lounger
    Join Date
    Aug 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Dates (2002, 2003)

    Windows regional settings are correctly set to european format, Short Date 03/01/2006 Long Date 03 January 2006
    One vital piece of information missing from the post is that this happens when the csv file is opened via VBA with something like
    Sub Get_Data1()
    FileName = "Test.csv"
    File = Application.GetOpenFilename("CSV Files (*.CSV), *.CSV", _
    , "Select the " & FileName)

    'Now open it
    Workbooks.Open FileName:=File

    end sub

  5. #5
    New Lounger
    Join Date
    Aug 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Dates (2002, 2003)

    One vital piece of information missing from the post is that this happens when the csv file is opened via VBA with something like
    Sub Get_Data1()
    FileName = "Test.csv"
    File = Application.GetOpenFilename("CSV Files (*.CSV), *.CSV", _
    , "Select the " & FileName)

    'Now open it
    Workbooks.Open FileName:=File

    end sub

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Dates (2002, 2003)

    The open method has a Local parameter that should be set to True.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    New Lounger
    Join Date
    Aug 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Dates (2002, 2003)

    Sam

    Thanks for the help. I looked for the Local parameter but could not find anything relevant in Help. Could you explain just a little more?

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Dates (2002, 2003)

    From XL 2003 help:
    <hr>
    Open method as it applies to the Workbooks object.

    Opens a workbook.

    expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)

    <snip>

    Local Optional Variant. True saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically US English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).
    <hr>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    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

    Re: Excel Dates (2002, 2003)

    All you need to do is to change your line:
    Workbooks.Open FileName:=File

    to
    Workbooks.Open FileName:=File, local:=true

    Steve

  10. #10
    New Lounger
    Join Date
    Aug 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Dates (2002, 2003)

    Many thanks for all the help. Far better than MS provided and the ...........
    Workbooks.Open FileName:=File, local:=true
    Worked a treat..
    Thanks for your time and expertise

Posting Permissions

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