Date Format YYYYMMDD

[Gwhaou]

Hello,

I have a Excel sheet in which dates are in this format : YYYYMMDD without "/" separation,

So I used "Record Macro" - DATE -> Convert to change in to this format : DD/MM/YYYY, here the code it got me out :

Range("A5", Range("A5").End(xlDown)).Select

    Selection.TextToColumns Destination:=Range("A5", Range("A5").End(xlDown)), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 5), TrailingMinusNumbers:=True

Since I'm a Beginner I don't know if it's the correct way to convert that because i don't understand the code itself.

I'm asking if there is any other way with simplier code

 

[bebo021999]

You can use formula in cell B5 then drag down:

=date(left(A5,4)+0,mid(A5,5,2)+0,right(A5,2)+0)

 

[Peter_SSs]

any other way with simplier code

It's the same code but abbreviated a bit

Range("A5", Range("A5").End(xlDown)).TextToColumns , xlDelimited, , , , , , , False, , Array(1, 5)

However, if your code works, does it matter what it looks like?

You can use formula in cell B5 then drag down:

=date(left(A5,4)+0,mid(A5,5,2)+0,right(A5,2)+0)

It isn't a problem, but you don't need the +0's since the DATE functions coerce the strings to numbers anyway.

=DATE(LEFT(A6,4),MID(A6,5,2),RIGHT(A6,2))

 

[Gwhaou]

It's for a Project, this is just a part of the entire code, so i'm trying to simplify the code to keep just what I need to be clear on the explanations.
Thanks for your help.

 

[Peter_SSs]

You're welcome. Thanks for the additional information.