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!

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

--
__________________________________
HTH

Bob

"Arne Hegefors" <(E-Mail Removed)> wrote in message
news:161F6B9C-26B3-4495-9549-(E-Mail Removed)...
> 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:

> Do those steps in Excel with the macro recorder on, that will supply the
> code.
>
> --
> __________________________________
> HTH
>
> Bob
>
> "Arne Hegefors" <(E-Mail Removed)> wrote in message
> news:161F6B9C-26B3-4495-9549-(E-Mail Removed)...
> > 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!
>
>
>

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" <(E-Mail Removed)> wrote in message
news:22E8B5F6-EF2C-4C7B-A5E5-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:161F6B9C-26B3-4495-9549-(E-Mail Removed)...
>> > 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!
>>
>>
>>