Text to Columns Wizard

[excelnoobnoob]

Hi,
I have a data connection to a csv in my excel workbook.
One of the columns contains dates in mm/dd/yy format.
Excel won't accept 'format as date', so I searched online and found a solution (use the Convert Text to Columns Wizard).
This works! The column is now formatted correctly as date.
The only issue is, whenever the data source refreshes, the column reverts to the original, general format.

Does anybody know what can be done to resolve such an issue?

 

[excelnoobnoob]

Private Sub Worksheet_Change(ByVal Target As Range) Dim LR As Long, i As Long, x If Intersect(Target, Range("C:C")) Is Nothing Then Exit Sub Else Application.EnableEvents = False Range("C:C").NumberFormatLocal = "mm/dd/yy;@" LR = Cells(Rows.Count, 3).End(xlUp).Row x = Range(Range("C2"), Cells(LR, 3)) For i = 1 To UBound(x) x(i, 1) = CDate(Format(x(i, 1), "##/##/####")) Next Range(Range("C2"), Cells(LR, 3)) = x End If Application.EnableEvents = True End Sub

Thanks ver much Takae, this worked perfectly !!