Date formatting in VBA

[geniusideas]

I have date from text file import into excel as below
January 22, 2013 11:21 am
What I want is to convert to the date format as 22/01/13 using VBA without time. I have the code but when I try with different PC the result is different.Even I try format cell directly never work.Please help with VBA code. Thanks

 

[Claus Busch]

Hi,

Am Tue, 12 Feb 2013 07:08:34 -0800 (PST) schrieb geniusideas:

I have date from text file import into excel as below
January 22, 2013 11:21 am
What I want is to convert to the date format as 22/01/13 using VBA without time. I have the code but when I try with different PC the result is different.Even I try format cell directly never work.Please help with VBA code. Thanks

your dates in column A:

Sub Test()
Dim LRow As Long

LRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:A" & LRow).NumberFormat = "mmmm dd, yyyy"
Range("A1:A" & LRow).TextToColumns Destination:=Range("A1"), _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 4), _
TrailingMinusNumbers:=True
Range("A1:A" & LRow).NumberFormat = "dd/mm/yy"
End Sub


Regards
Claus Busch