Results 1 to 11 of 11
  1. #1
    dhullinger
    Guest

    Date format not recognized in data import

    When importing date information into Excel from an external source, the data looks correct, but is in the format it was imported in (mm/dd/yy). If I try to change the format, it is not changed. The cell is not formatted as text. The only thing that makes the change work is if the cell is active and I enter edit mode (by hitting F2), then the cell will accept any formatting I want.

    Any ideas? On a spreadsheet that has 1000 rows of data, it takes a while to hit F2 in every cell.

    Thanks.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format not recognized in data import

    When you're importing, you can specify that one column is to be a particular format- have you done that?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    dhullinger
    Guest

    Re: Date format not recognized in data import

    Unfortunately, I can't. It is data that is dumped from a 3rd party application (FactSet, FYI) and I do not have any control until it is too late.

  4. #4
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format not recognized in data import

    Formatting dates (on this side of the Atlantic) I hold responsible for most of my hair loss.
    I import date data from an application that literally goes:
    30/11/2000
    12/01/00
    12/02/00
    ~~~~~~~~
    12/11/00
    12/12/00
    13/12/2000
    etc.
    No attempts at formatting the column appear to resolve it and I eventually wrote a macro to detect the length of the date and, if 8, reformat it.
    Do you have a similar scenario?

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Date format not recognized in data import

    You can address this formulaically. If the first column is input, and the second column the desired output (using mm/dd/yyyy format):
    1/1/2000 ----- 01/01/2000
    1/22/2000 ---- 01/22/2000
    12/11/01 ----- 12/11/2001
    12/11/90 ----- 12/11/1990

    I haven't stress-tested all possibilities, but the following should work for dates between 1910 and 2010:

    =DATE(RIGHT(A2,LEN(A2)-FIND("/",A2,FIND("/",A2)+1))+IF(LEN(A2)-FIND("/",A2,FIND("/",A2)+1)>3,,IF(VALUE(RIGHT(A2,LEN(A2)-FIND("/",A2,FIND("/",A2)+1)))<10,2000,1900)),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,FIND("/",A2,FIND("/",A2)+1)-FIND("/",A2)-1))

    This can probably be improved on, but I need to get a life.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format not recognized in data import

    Thanks for the response - I'll look into your suggestion.
    You did notice that my input was changing from dd/mm to mm/dd depending on whether the day was <13?

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Date format not recognized in data import

    I confess I overlooked the issue of day <13; if you are dealing with mixed mm/dd/yy and dd/mm/yy data you have my very deepest sympathy. It occurs to me that your source data -REALLY- needs to move to 4-digit yyyy outputs!
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format not recognized in data import

    How are you importing from the external source?

    The formatting I mentioned is when you open up a document from Excel in plain unformatted text- you get a text Import Wizard. On that window, if you select the appropriate options, then select "next" twice, screen 3 od 3 allows you to select any particular column, and to specify the format for that column. You can specify different date formats.

    Of course if you're importing by other means you'll need a differenct approach.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format not recognized in data import

    In XL97 the formatting facility in the Import Data Wizard is also available in the Text to Columns Wizard (Data menu), so you should be able to use it on the spreadsheet you have been given. Just select the column you want to reformat then activate the TTCW.

  10. #10
    smac
    Guest

    Re: Date format not recognized in data import

    Check the file extension. Excel automatically opens up .CSV files without giving you the option of defining column types.

    Change this to a .TXT extension and you should get the option to define the import. The downside is you are then forced to carry out some manual interaction on the import.

    If you're up on ODBC, you can try to access the text file as an ODBC data source. The advanced configuration options allow you to define the format for each column, but this is pretty hairy stuff...

    Cheers,
    Smac

  11. #11
    smac
    Guest

    Re: Date format not recognized in data import

    One tip that has worked for me in the past: before importing the data, change your regional settings to match those of the source data.

    On Windows, use Start>Setting>Control Panel>Regional Settings.

    You'll need to change the Date>short date format.

    Depending on the OS, you may get away without a reboot. Once in Excel, save the file as an Excel spreadsheet and you can revert to your original settings.

    Cheers,
    Sean

Posting Permissions

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