Excel: Converting a date column in a text file

[duncang]

My problem is Excel2002 changing the dd/mm/yyy format to mm/dd/yyyy when loading a text file.

Given the txt file (format dd/mm/yyy);

Time
1/01/1971 23:53
2/01/1971 0:05

I can’t come up with any VBA code to open the file so it has the correct date values (25934.995, 25935.003).

I’ve tried the following macro.

    Workbooks.OpenText Filename:= _
        "C:\ \ADCoutputd.txt", FieldInfo:=Array(1, xlDMYFormat)

It converts the file to
Time
1/01/1971 23:53
1/02/1971 0:05
,swapping the days and months.

I can manually open the file correctly, but when I record this and play it the code swaps the days and months.

I don’t have this problem in Excel2000.
My regional short date format is d/MM/yyyy.

Any ideas?

 

[SkipVought]

Hi,

Here's my file data...

Skip+1/12/2002
John+31/12/2002
[\code]
Here's my code...
[code]
    Workbooks.OpenText _
        Filename:="MyDate.TXT", _
        DataType:=xlDelimited, _
        Other:=True, _
        OtherChar:="+", _
        FieldInfo:=Array(Array(1, xlGeneralFormat), Array(2, xlDMYFormat))
[\code]
Here's my result...
[code]
Skip	1/12/02
John	31/12/02
[\code]
Formatting shows that my month is December.

Hope this helps  :-)
 Skip,
SkipAndMary1017@mindspring.com

 

[duncang]

Thanks for the suggestion Skip,
Your solution works for the MyDate.txt file.
Though when MyDate.txt includes time;

Time
Skip+23:53 1/01/1971
John+0:05 2/01/1971
Then the day and month are still the wrong way round.

I then removed time from my original file (ADCoutputd.txt) and excel loaded the file with the day and month the wrong way round.

So I still haven't got a full solution, but I'm closer.

Cheers
Duncan

 

[SkipVought]

Then try putting the data and time into 2 different columns. Skip,
SkipAndMary1017@mindspring.com