Date formatting in VBA

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
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top