Dates with "." for a delimiter

[Weaver]

I'm importing a text file via VB using a recorded version of the "import external data" routine. The data consists of dates and times but they're stored with "." rather than "/". So I used find/replace to switch the "." for "/" and all was okay, unless there's a possibility of interpreting the date in US format. So 31.05.2011 becomes 31/05/2011 (31st May), but 01.06.2011 (1st June) becomes 06/01/2011 (6th Jan) and so on. Assuming I'd rather not convert each cell one at a time, is there a way round this? The default date formatting for the system is the UK version and I set the numberformat for the column with the dates in to dd/mm/yyyy hh:mm before doing the search replace, to no effect.

Any ideas? I think must be missing something obvious.

 

[Jon von der Heyden]

In your import wizard there is a data format step (step 3 of text to columns wizard).

You should format that column of dates as DMY in that step. No find & replace required...

 

[GlennUK]

Instead of doing Find/Replace, do Data/Text To Columns/Date=DMY

 

[Joneye]

Small suggestion, when Ive imported data before ive often had to clear formatting.

I hope a new workbook, copy any sell, then go back to the imported data, highlight ALL cells then right click, paste special then go to OPERATION and click ADD then ok.

This often clears odd formatting that sticks with imports. May or may not work but worth a try.

 

[Weaver]

In your import wizard there is a data format step (step 3 of text to columns wizard).

You should format that column of dates as DMY in that step. No find & replace required...

Tried that to begin with but it doesn't work, due to the data containing "." instead of "/" in its native format.

.TextFileColumnDataTypes = Array(4, 9, 1, 9)

I got round it in the end by inserting an extra column, using the formula

"=substitute(rc[1],""."",""/"")"

turning the result of that to text, doing 'text to columns' with no delimiters and deleting the original data.

 

[Jon von der Heyden]

It works for me with a "." and I've never seen it fail before...

 

[Weaver]

It works for me with a "." and I've never seen it fail before...

I'm wondering if it's a localisation thing. What happens if you type in a date using "." instead of "/"? Would Excel recognise it as a date? It keeps it as a string over here. Or could it be because there are times as well as dates in the data? It's not the first time I've had problems with this, but before it was Access and I got round it with a schema.

 

[Weaver]

Interestingly, if you preformat the column as "dd.mm.yyyy hh:mm"

then do the find/replace manually, it only converts the cells to dates where the first two digits could represent a month (i.e less than 13)

 

[Jon von der Heyden]

It's a string by default where ever you go. The import data wizard will (*should) convert it from a text string to a date value.

I'm wondering if it's a localisation thing. What happens if you type in a date using "." instead of "/"? Would Excel recognise it as a date? It keeps it as a string over here. Or could it be because there are times as well as dates in the data? It's not the first time I've had problems with this, but before it was Access and I got round it with a schema.

 

[Weaver]

Even when using 2002 on an XP system?