How to format date when converting text to columns?

Question

I'm trying to convert a range of data, from text to columns, with VBA.

When I apply the code some cells with date don't convert as date. If then you enter the cell and press "enter" it converts to date format.

Selection.TextToColumns _
    Destination:=Range("A1"), _
    DataType:=xlDelimited,  _
    TextQualifier:=xlDoubleQuote,  _
    ConsecutiveDelimiter:=False,  _
    Tab:=True,  _
    Semicolon:=False,  _
    Comma:=False,  _
    Space:=False,  _
    Other:=True,  _
    OtherChar:="$",  _
    FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _
                     Array(6, 4), Array(7, 8), Array(8, 4), Array(9, 4), Array(10, 4), _
                     Array(11, 4), Array(12, 4), Array(13, 1), Array(14, 1), Array(15, 1), _
                     Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
                     Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), _
                     Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), _
                     Array(31, 1), Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), _
                     Array(36, 1), Array(37, 1), Array( 38, 1), Array(39, 1), Array(40, 1), _
                     Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), _
                     Array(46, 1)), 
    DecimalSeparator:=".",  _
    ThousandsSeparator:=",", _
    TrailingMinusNumbers:=True

In the array, cells 6 to 12 is where the dates are.

I am not able to reproduce problem. Maybe there is an issue with format set to "Text" for a few problematic cells. You should try debug specific situation when the problem occurs. — Rafał B.
Need to see a sample of your data. Perhaps you are not setting the XlColumnDataType according to the format of the data in that column. — Ron Rosenfeld

Marcos R Marcos R · Accepted Answer · 2019-08-22T12:06:14

I cannot update any of the data due to data protection policy. But here is a small sample where also happens the same problem. All the text is in the same column A1 and each line in a different row

20191284809$$BT$Anulado$23/05/2019 09:22:57

20191304047$$BT$Anulado$04/06/2019 20:29:03

20191159599$$BT$Anulado$01/03/2019 11:53:10

20191165919$$BT$Anulado$06/03/2019 11:33:18

20191247883$$BT$Anulado$27/04/2019 14:54:17

20191381935$$BT$Cerrado$22/07/2019 01:12:03

20191404309$$BT$Cerrado$03/08/2019 08:56:55

How to format date when converting text to columns?

1 Answers