Need help to fix import csv to date problem

Wellie

New Member
Joined
Nov 2, 2004
Messages
4
I have a csv files generated by an application which contains a lot of date columns. I started simply using the following code to import the csv into Excel 2003 and then tried on 2007 using a macro which I prepared
Workbooks.Open Filename:="J:\Week Ending RFS 2011-01-07.txt"
I noticed a problem that the day and month of the date field randomly interchanged.
I then tried to manuel open the csv file, it prompts the Text Wizard (without all default settings), and I clicked Next twice and then Finish. It correctly impoted the file & shows the date in dd/mm/yyyy format. So, I ran the Record Marco to caputre the VBA code and replace ot with my 1 liner code as shown below as part of overall data process codes.
Workbooks.OpenText Filename:= _
"J:\Week Ending RFS 2011-01-07.txt", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), ... Array(89, 1)), TrailingMinusNumbers:=True

However, when I ran my code and step thru it, first, it does not prompt the Text Wizard, Second, randomly, some of the dates changed format from dd/mm/yyyy to mm/dd/yyyy.

I also noticed that those with remains (dd/mm/yyyy) is left justified (as General cell format) while the converted cells (with mm/dd/yy) is right justified (as Date *dd/mm/yyy format) but my actual data has already be converted with the day and month interchanged.

I need to perform data analysis by date which must be in a consisten format.

Can someone please help me how to fix this auto date format problem ?
 
Re: Need help to .... date problem - Dupplicated - Sorry

Oop! Somehow, I submitted twice by accident.
Sorry for the confusion.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top