Glaswegian
06-01-2009, 04:21 AM
Hi again
I'm importing a large text file, 120,000 rows, using Chip Pearson's code - this all works fine. However, I have found a problem with some dates. Some, not all, appear in Excel as US formatted dates - mm/dd/yyyy. Looking at the original data in the text file, the format is as I would expect - UK format - dd/mm/yyyy. In Excel, if I format the column in question to General, the US style dates change to a numerical value - which suggests to me they are being seen as real dates. The UK style dates remain unchanged - which suggests they are being seen as text. Dates appear in the formula bar exactly as they appear on the worksheet. I've already tried to format the column using some code at the end of the main import routine, but it does not make any difference. Neither does manually formatting once the import routine is finished. I could use a formula to change the US style values, but since there is no pattern to this, it would have to be done manually (I think) - not a job that appeals to me. No matter what I do, I cannot make the US style dates appear as they should - UK style dates. Is this a problem with the text file, with Excel or with me? :dunno Any help or guidance greatly appreciated.
I'm importing a large text file, 120,000 rows, using Chip Pearson's code - this all works fine. However, I have found a problem with some dates. Some, not all, appear in Excel as US formatted dates - mm/dd/yyyy. Looking at the original data in the text file, the format is as I would expect - UK format - dd/mm/yyyy. In Excel, if I format the column in question to General, the US style dates change to a numerical value - which suggests to me they are being seen as real dates. The UK style dates remain unchanged - which suggests they are being seen as text. Dates appear in the formula bar exactly as they appear on the worksheet. I've already tried to format the column using some code at the end of the main import routine, but it does not make any difference. Neither does manually formatting once the import routine is finished. I could use a formula to change the US style values, but since there is no pattern to this, it would have to be done manually (I think) - not a job that appeals to me. No matter what I do, I cannot make the US style dates appear as they should - UK style dates. Is this a problem with the text file, with Excel or with me? :dunno Any help or guidance greatly appreciated.