I am struggling with the problem that MS changed the txt import
procedure in Excel. We open txt files in Excel automatically from our
ERP system. The ERP system creates a txt file (in this file all dates
are fine), than Excel starts and through VBA the txt file is opened
and some manupilation is done.

The problem now is that dates are changed to numbers in the wrong
format. In the txt files I have the following dates:

23-01-2008 ddmmyyyy
30-02-2008 ddmmyyyy
07-03-2008 ddmmyyyy
10-04-2008 ddmmyyyy

The first two dates are well displayed in Excel after import, but the
other two are displayed as numbers. If I go into the cell properties
and change the field to date, the date displayed is

03-07-2008 mmddyyyy (note that the date format is still ddmmyyyy)
04-10-2008 mmddyyyy (note that the date format is still ddmmyyyy)

And this is very frustrating, Excel swithces the days and the months
if this is possible. Why can Excel not just import the txt file as it
is? Of course I can use VBA to find this dates and format them as
mmddyyyy, but in my opinion that can not be the way.

I would like to know if there is a way to import the txt file in Excel
and that the dates are displayed as they should.

Thanks in advance.

Dirk

Try formating the cells before you import the data. I sually write my own
VBA code to import Test files because of ALL the problems people have with
excel translation the imported data incorrectly.

"Doekoe" wrote:

I am struggling with the problem that MS changed the txt import
procedure in Excel. We open txt files in Excel automatically from our
ERP system. The ERP system creates a txt file (in this file all dates
are fine), than Excel starts and through VBA the txt file is opened
and some manupilation is done.

The problem now is that dates are changed to numbers in the wrong
format. In the txt files I have the following dates:

23-01-2008 ddmmyyyy
30-02-2008 ddmmyyyy
07-03-2008 ddmmyyyy
10-04-2008 ddmmyyyy

The first two dates are well displayed in Excel after import, but the
other two are displayed as numbers. If I go into the cell properties
and change the field to date, the date displayed is

03-07-2008 mmddyyyy (note that the date format is still ddmmyyyy)
04-10-2008 mmddyyyy (note that the date format is still ddmmyyyy)

And this is very frustrating, Excel swithces the days and the months
if this is possible. Why can Excel not just import the txt file as it
is? Of course I can use VBA to find this dates and format them as
mmddyyyy, but in my opinion that can not be the way.

I would like to know if there is a way to import the txt file in Excel
and that the dates are displayed as they should.

Thanks in advance.

Dirk