Macro to convert text to date
 

I've been trying to record a Macro so that I can change text in columns to a
date format, I've used Text to columns to do this which works well outside a
Macro, but I have 20 columns to do on a weekly basis so would like to
automate it in a macro.

The imported file is in the following format "04/04/2005 10:17:51"
dd/mm/yyyy hh:mm:ss

I recorded a Marco, but it does not work in cells with a date 13+ for
example 13/04/2005 10:17:51 remains in text format?

Can any one let me know what I'm doing wrong or suggest VB code to solve this.

My knowledge of VB is poor, so go easy on me!

You seem to be using US regional settings but the date text is in UK format.
Hence excel cannot convert it automatically into a date. The solution is to
switch your regional settings to English(United Kingdom) then do the Text to
columns changes and after completing the macro, save and close the workbook
and switch back to whatever your original regional setting was. Then open
your workbook and all dates should be OK.

Alok Joshi

"Nortos" wrote:

I've been trying to record a Macro so that I can change text in columns to a
date format, I've used Text to columns to do this which works well outside a
Macro, but I have 20 columns to do on a weekly basis so would like to
automate it in a macro.

The imported file is in the following format "04/04/2005 10:17:51"
dd/mm/yyyy hh:mm:ss

I recorded a Marco, but it does not work in cells with a date 13+ for
example 13/04/2005 10:17:51 remains in text format?

Can any one let me know what I'm doing wrong or suggest VB code to solve this.

My knowledge of VB is poor, so go easy on me!

My regional settings are UK, so it's not that and the text to columns works a
line at a time great, but when you record a macro that does it for a column
then another and so on it does not work corectly, see VB code example below:

Range("F14").Select
Application.Run "'Stage Report Test.xls'!ReturnToStartCells"
Sheets("Data import").Select
Columns("W:W").Select
Selection.TextToColumns Destination:=Range("W1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True
Columns("X:X").Select
Selection.TextToColumns Destination:=Range("X1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True
Columns("Y:Y").Select
Selection.TextToColumns Destination:=Range("Y1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True
Columns("Z:Z").Select
Selection.TextToColumns Destination:=Range("Z1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True
Range("A2").Select
Sheets("Macro").Select
Range("F14").Select
End Sub

Any ideas?

"Alok" wrote:

You seem to be using US regional settings but the date text is in UK format.
Hence excel cannot convert it automatically into a date. The solution is to
switch your regional settings to English(United Kingdom) then do the Text to
columns changes and after completing the macro, save and close the workbook
and switch back to whatever your original regional setting was. Then open
your workbook and all dates should be OK.

Alok Joshi

"Nortos" wrote:

I've been trying to record a Macro so that I can change text in columns to a
date format, I've used Text to columns to do this which works well outside a
Macro, but I have 20 columns to do on a weekly basis so would like to
automate it in a macro.

The imported file is in the following format "04/04/2005 10:17:51"
dd/mm/yyyy hh:mm:ss

I recorded a Marco, but it does not work in cells with a date 13+ for
example 13/04/2005 10:17:51 remains in text format?

Can any one let me know what I'm doing wrong or suggest VB code to solve this.

My knowledge of VB is poor, so go easy on me!