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. E.g. Original data is 07/01/2011 (7/Jan/2011) and it shows as 01/07/2011 (1/Jul/2011)
I then tried to manually 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 (i.e. Original data is 07/01/2011 as expected). 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 format still got changed from dd/mm/yyyy to mm/dd/yyyy which is annoying.
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. I had looked thru the web and not able to find answers that can help (or there may be answer somewhere out there not yet found by me)
Can someone please help me how to fix this auto date format problem ?
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. E.g. Original data is 07/01/2011 (7/Jan/2011) and it shows as 01/07/2011 (1/Jul/2011)
I then tried to manually 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 (i.e. Original data is 07/01/2011 as expected). 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 format still got changed from dd/mm/yyyy to mm/dd/yyyy which is annoying.
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. I had looked thru the web and not able to find answers that can help (or there may be answer somewhere out there not yet found by me)
Can someone please help me how to fix this auto date format problem ?
Last edited:
