VBA Convert Text to Dates

rhombus4

Active Member
Joined
May 26, 2010
Messages
422
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
 

rhombus4

Active Member
Joined
May 26, 2010
Messages
422
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks Alex. That seems to do everything as needed :)

I did manage to work out a temporary solution which was really quick for one column but didn't know how to put in an array

VBA Code:
Sub ConvertDates()
Dim lastRow As Long: lastRow = Sheet1.Range("M99999").End(xlUp).Row

With Range("BA2:BA" & lastRow) 'Helper Column
.Value = "=IFERROR(IF(M2="""","""",IF(ISTEXT(M2),DATEVALUE(M2),M2)),M2)"
Range("M2:M" & lastRow).Value = .Value
.Replace "False", ""
End With

End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Watch MrExcel Video

Forum statistics

Threads
1,130,083
Messages
5,639,977
Members
417,120
Latest member
Pavithra devi

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