Hi
I am trying to format a column of numbers, an example being:
03/11/2012 to change to 11-Mar-12
02/21/2012 to change to 21-Feb-12
When trying to format the orignal column they wont budge, I have been able to get them to the format I want by using
Data>Text To Column>Next>Next>Change Date format to MDY>Change Destination>Finish>Select new column>Change format to dd-mmm-yy.
This changes one column of
03/11/2012
02/21/2012
over to
11-Mar-12
21-Feb-12
When I do this manually I get the correct date, when I record the macro I get the correct date. When I play the macro back the dates I actually get are
03-Nov-12
21-Feb-12
Am I right in thinking that the change Date format to MDY in the Text to Column has not been picked up by the recording of the macro? If so how do I get it into the code?
Selection.TextToColumns Destination:=ActiveCell.Offset(0, 1).Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 3), TrailingMinusNumbers:=True
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "dd-mmm-yy"
Thanks in advance.
And for anyone that's looking for a quick way to get rid of those pesky hh:mm:ss am/pm stamps. Data>Text To Column>select fixed width>Next>Add your break lines.
I am trying to format a column of numbers, an example being:
03/11/2012 to change to 11-Mar-12
02/21/2012 to change to 21-Feb-12
When trying to format the orignal column they wont budge, I have been able to get them to the format I want by using
Data>Text To Column>Next>Next>Change Date format to MDY>Change Destination>Finish>Select new column>Change format to dd-mmm-yy.
This changes one column of
03/11/2012
02/21/2012
over to
11-Mar-12
21-Feb-12
When I do this manually I get the correct date, when I record the macro I get the correct date. When I play the macro back the dates I actually get are
03-Nov-12
21-Feb-12
Am I right in thinking that the change Date format to MDY in the Text to Column has not been picked up by the recording of the macro? If so how do I get it into the code?
Selection.TextToColumns Destination:=ActiveCell.Offset(0, 1).Range("A1"), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 3), TrailingMinusNumbers:=True
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "dd-mmm-yy"
Thanks in advance.
And for anyone that's looking for a quick way to get rid of those pesky hh:mm:ss am/pm stamps. Data>Text To Column>select fixed width>Next>Add your break lines.