importing a text file - problem with dates "Americanising"

[Guest]

Hiya,

I frequently import text files into Excel through macro's and have found
that moving onto Excel 2003 has resulted in the dates being reversed.

i.e 8th December 2005 08/12/05 being imported as 12/08/05. I have checked
the formatting in Excel and this does not appear to be the issue. It appears
that the data is 12/08/05 rather then the expected 08/12/05.

When I revert back to Excel 98/2000 everything works fine. I have tried
recreating the macros in 2003 which doesn't seem to solve the issue.

Any thoughts / ideas appreicated.

Thanks

Simon

 

[Dave Peterson]

Name the text file *.txt. Then record a macro when you do the import. You'll
be able to specify mdy or dmy or whatever you want for each date field.

 

[Guest]

Have amended the import macro so that particular array now has the "switch"
set to number 4 rather than 1, the results are the same. The thing I forgot
to mention in the original string is that some of the file imports ok (with
correct dates) but some lines import incorrect!!! Confuses me as the data is
consistent in the underlying text file! Maybe a Microsoft bug?!?

 

[Dave Peterson]

I'd guess it's not a bug.

But maybe you're looking at the field and seeing a date and assuming it's
correct.

In a text file, 01/02/03 would look like a date.

Depending on your windows setting, it may even import like a date--but it may
not be the date that it's supposed to be.

I'd format those date fields in an unambiguous format: mmmm dd, yyyy
and then look to see if each field got imported correctly--not just as a date.