VBA Convert Text to Dates

rhombus4

Active Member
Joined
May 26, 2010
Messages
413
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

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

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,436
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
Hello,

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

VBA Code:
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

Active Member
Joined
May 26, 2010
Messages
413
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

VBA Code:
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

Well-known Member
Joined
Jan 20, 2014
Messages
1,436
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
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:

Rich (BB code):
"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

Active Member
Joined
May 26, 2010
Messages
413
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Thanks again.

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

rhombus4

Active Member
Joined
May 26, 2010
Messages
413
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
560
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
May 26, 2010
Messages
413
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
560
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
May 26, 2010
Messages
413
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,380
Messages
5,635,919
Members
416,887
Latest member
SheriE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top