delimited text file: wrong date interpretation

J

Jerry H

I am able to open a text file that is delimited by tab. One of the columns in the source is in the format dd/mm/yyyy

Three sample data are
02/04/200
08/04/200
17/04/200

I wish Excel to interpretate all data as having April for month But no matter what I do under the 'text import wizard menu', Excel always thinks the format is mm/dd/yyyy and because it does not like the third piece of data, it is treated as text by Excel

Because I couldn't find any solution, I am now forced to write a VBA script to achieve what I need. But do I miss any simple answer to the following 2 questions

Question 1
What is the simplest way to instruct Excel to intepretate my data as DD/MM/yyyy
Question 2
How to ask Excel to import this column as pure text all its way?

Our work environment has both Excel 97 and Excel 2000
 
A

Aladin Akyurek

At the 3rd step of the Text to Columns Wizard, choose DMY for Column data
format.

Jerry H said:
I am able to open a text file that is delimited by tab. One of the columns
Click to expand...
in the source is in the format dd/mm/yyyy.
Three sample data are:
02/04/2004
08/04/2004
17/04/2004

I wish Excel to interpretate all data as having April for month But no
Click to expand...
matter what I do under the 'text import wizard menu', Excel always thinks
the format is mm/dd/yyyy and because it does not like the third piece of
data, it is treated as text by Excel.
Because I couldn't find any solution, I am now forced to write a VBA
Click to expand...
script to achieve what I need. But do I miss any simple answer to the
following 2 questions:
 
Top