Date format not recognized in data import

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote