Date formatting in VBA

  • Thread starter Thread starter geniusideas
  • Start date Start date
G

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
 
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
Click to expand...

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
 
Back
Top