convert US date format to Excel readable
 

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!

convert US date format to Excel readable
 

Do those steps in Excel with the macro recorder on, that will supply the
code.

--
__________________________________
HTH

Bob

"Arne Hegefors" wrote in message
...
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!

convert US date format to Excel readable
 

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:

Do those steps in Excel with the macro recorder on, that will supply the
code.

--
__________________________________
HTH

Bob

"Arne Hegefors" wrote in message
...
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!

convert US date format to Excel readable
 

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?

--
__________________________________
HTH

Bob

"Arne Hegefors" wrote in message
...
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:

Do those steps in Excel with the macro recorder on, that will supply the
code.

--
__________________________________
HTH

Bob

"Arne Hegefors" wrote in message
...
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!