Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Jersey, UK

    Unanswered: CSV File Open Changes Date Format

    Hi all,

    I've searched the 'net high and low for a solution to this problem and "still haven't found what I'm looking for" (as U2 would sing).

    The problem is the US/UK date conflict and it's easy to replicate (at least if you're on a UK Regional Setting - I haven't tried it on US settings for obvious reasons). Here's how to replicate the problem:

    create a new s/sheet;
    enter dates like this '01/01/2001' through to '01/02/2001' (dd/mm/yyyy) so you'll have 32 records in total;
    save the s/sheet as a CSV file and close;
    open the CSV file in Notepad and note the date values - all as expected;
    now open the s/sheet once inside Excel - note the date formats;
    and now open from Windows Explorer - note the date format is different;

    This is the problem. As an aside you can see what's happening to some fields if you use the ISTEXT() function - some fields are not being recognised as dates. This is because Excel is being a prat and is trying to format data into a US date format even when I haven't asked it to.

    Does anyone know how to turn off this feature. I know it's possible because it used to work on another pc I used (for another employer). And I know about all the other workarounds, e.g. .txt extension, Clarion date format, etc, etc. I just want the feature turned off.

    Regards - RC

  2. #2
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 8
    Have had the same problem with date in US/UK

    what I have done is put the month as the name

    '01 jan 2001' not 01/01/01
    hope this help

    See clear as mud

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

Posting Permissions

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