VBA Convert Text to Dates

[rhombus4]

If I manually Find and Replace / or a space in a column any dates formatted as text will become dates

However when I tried to use the Macro recorder it doesn't work, some of the dates are still text

Its also really slow as it needs to convert alot of rows

Is there a quick way using VBA to convert several columns. I need to convert columns B, D, L, M and X

    Columns("M:M").Select
    Selection.Replace What:="/", Replacement:="/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

 

[BarryL]

Hello,

Can you try the blow code on column M and see if it works?

Sub Test()
    Columns("M:M").Replace What:=" *", Replacement:="", LookAt:=xlPart
    Columns("M:M").TextToColumns Destination:=Range("M1"), _
        DataType:=xlDelimited, FieldInfo:=Array(1, xlDMYFormat)
    Columns("M:M").NumberFormat = "dd/mm/yyyy"
End Sub

Credit: Tetra201

 

[rhombus4]

Thanks Barry

The above worked for column M. If I need to do it for the other columns, (B, D, L, M and X) would I need to repeat the code above individually for each column, or can it be done in one go.

PS what does the Array(1, xlDMYFormat) do and Destination:=Range("M1") Does it matter If i change this to M2 as M1 is the heading


I previously had code below which worked but was very slow

On Error Resume Next
For i = 2 To n
    Cells(i, "b").Value = CDate(Cells(i, "b").Value)
    Cells(i, "d").Value = CDate(Cells(i, "d").Value)
    Cells(i, "l").Value = CDate(Cells(i, "m").Value)
    Cells(i, "m").Value = CDate(Cells(i, "l").Value)
    Cells(i, "x").Value = CDate(Cells(i, "x").Value)
Next i
On Error GoTo 0

 

[BarryL]

No worries, Text to columns only works on 1 column at a time so im pretty sure you have to repeat it for the columns you want. But in my testing it was pretty quick so that shouldnt be an issue.

Array(1, xlDMYFormat) is given to FieldInfo. FieldInfo can be defined as:

"An array containing parse information for the individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the xlColumnDataTypeconstants specifying how the column is parsed."

My crude explanation of this is, this is where we are assigning the Date datatype to this column. (correct me if I'm wrong anyone).

M1 should not be changed to M2. As you are replacing the whole column, if you specified M2 i'm pretty sure that would throw an error. This should have no mpact on the heading though.

 

[rhombus4]

Thanks again.

I'll repeat for each column and leave M1 as it is

 

[rhombus4]

Hi just noticed an issue. I thought it worked but

When its run on columns that have dates already formatted as dates, for those it switches the day and month

i.e. 05/08/20 switches to 08/05/20. the only ones it wont switch are the ones it can't change i.e. 14/12/20 as it cant changed to 12/14/20

 

[Alex Blakenburg]

Where are you getting your data from and in what form ?
If you have data coming in as 14/12/20 and you believe this to be 14 Dec 20, then your source data is using a US formatted date.
The text to column needs you to specify the date format of the source data not the format you want it to be in.
The format you want it to be in comes from your regional settings.

Change your code from xlDMYFormat to xlMDYFormat

You do need to know be confident that the source data is going to be consistent though otherwise you need address it at the source.

You might want to consider using Power Query, the equivalent function there is "Using Locale..." > Select Data Type Date and Locale English (Unites States)

 

[rhombus4]

The source data is from another workbook which I copy in then try to convert.
The dates in the source are correct so 14/12/20 comes in as 14/12/20
The issue is some dates in the column/s are formatted as text and some are ok

When I run the code in post 2 I thought it worked as the ones formatted as text became proper dates, however the ones already formatted as dates changed the months and dates i.e 08/06/20 became 06/08/20 and everytime the code was run it kept switching

So went 08/06/20 then 06/08/20 then 08/06/20 etc

 

[Alex Blakenburg]

What is your default date format ? dd/mm/yyyy or mm/dd/yyyy (what country are you in).
Also show me the code you finished up with using.

 

[rhombus4]

Default is dd/mm/yyyy I'm in the UK

Code used is below and I repeated it for the other columns used

Sub Test()
Columns("M:M").Replace What:=" *", Replacement:="", LookAt:=xlPart
Columns("M:M").TextToColumns Destination:=Range("M1"), _
DataType:=xlDelimited, FieldInfo:=Array(1, xlDMYFormat)
Columns("M:M").NumberFormat = "dd/mm/yyyy"
End Sub