• Date format not recognized in data import

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » 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.


    Viewing 2 reply threads
    • #1774919

      When you’re importing, you can specify that one column is to be a particular format- have you done that?

      • #1775011

        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.

        • #1775017

          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:
          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?

          • #1775045

            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:


            This can probably be improved on, but I need to get a life.

            • #1775047

              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?

            • #1775048

              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!

        • #1775098

          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.

          • #1775382

            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…


    • #1775217

      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.

    • #1775383

      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.


    Viewing 2 reply threads
    Reply To: Date format not recognized in data import

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: