convert US date format to Excel readable

  • Thread starter Thread starter Arne Hegefors
  • Start date Start date
A

Arne Hegefors

Hi! I often import data from other programs to Excel and often the date
formats of those files is of a type that Excel does not understand. Normally
they look like this 12/31/2012 (ie mm/dd/yyyy). now i want to be able to
convert these dates to excel readable dates. i can do this choosing text to
columns and then do some stuff but that takes some time if there are many
series. Is there any way I can write a macro that does this for me? any idea
on how to do that? Thanks alot for your help!
 
Hi Bob. Thanks for your comment! I did record a macro and some adjustments
but I have few problems. When I record the macro the start of the range where
the data is to be plotted is fixed (the first line in my code
...:=Range("A1"). ) Laso I want to be able how long the selection is form the
beginning so that I can adjust the loops. please see my code below. thanks

Sub Makro3()

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=True,
OtherChar:= _
"/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True


Dim i As Long 'number of rows
Dim j As Long 'number of columns

For j = 0 To 2 'loop columns
For i = 0 To 10 'loop rows
If Range("a1").Offset(i, j) < 10 Then
Range("a1").Offset(i, j).NumberFormat = "@"
Range("a1").Offset(i, j).NumberFormat = "@"
Range("a1").Offset(i, j) = 0 & Range("a1").Offset(i, j)
End If
Next
Next

For i = 0 To 10
Range("a1").Offset(i, 0) = Range("a1").Offset(i, 2) &
Range("a1").Offset(i, 1) & Range("a1").Offset(i, 0)
Next


End Sub



"Bob Phillips" skrev:
 
Sorry Arne, it is not quite clear to me.

If you select the whole columns doesn't that code work fine? You seem to be
saying that you need to know the last row, but why?
 
Back
Top