(Solved) – Text to Column (Date) using current year instead of the timestamp

(solved)-–-text-to-column-(date)-using-current-year-instead-of-the-timestamp

I’m using text to columns as part of a VBA macro to separate timestamps into 2 other columns. When I format Column B to dd/mm/yyyy it uses the current year 2020 instead of 2019. Is there a way to adjust my macro to pull the year from the original timestamp or alternatively, pull the year from Column C once Text to Columns has completed?

Reference1

Reference2

Range("A5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("A5"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 3), Array(2, 3), Array(3, 3)), TrailingMinusNumbers:=True
    Range("B5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "dd/mm/yyyy;@"
    Selection.TextToColumns Destination:=Range("B5"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 8), TrailingMinusNumbers:=True

Leave a Reply

Your email address will not be published. Required fields are marked *