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
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
607
Office Version
  1. 365
Platform
  1. Windows
Thanks. That seemed to do the trick for Column M is it possible to do the same for the other columns B, D, L, M and X or do I have to repeat code above for each one

Also one thing I noticed is I can do it manually in several ways, but when i used macro recorder it would only do the ones where the first part was less than or equal 12. Also the Divide and Multiply Option went right down to the Bottom for Skip Blanks = False and Skip Blanks =True option

4 Methods to manually convert
1) Replace & Find /with /
2) Copy a Blank Cell/Paste Special over the Column with Operation Add/Then manually format as dd/mm/yyyy
3) Copy a cell with a 1 in it/Paste Special over the Column with Operation Multiply/Then manually format as dd/mm/yyyy
4) Copy a cell with a 1 in it/Paste Special over the Column with Operation Divide/Then manually format as dd/mm/yyyy

But when done via VBA it wouldn't do for any day above 12 e.g. 13/05/20

VBA Code:
Sub ConvertText()
With Columns("A:A")
  .Replace What:="/", Replacement:="/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End With

  Range("J1").Copy 'Blank Cell
With Columns("B:B")
  .PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, _
        SkipBlanks:=False, Transpose:=False
  .NumberFormat = "dd/mm/yy"
End With
  Application.CutCopyMode = False
  
  Range("J2").Copy 'Contains 1
With Columns("C:C")
  .PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False
  .NumberFormat = "dd/mm/yy"
End With
  Application.CutCopyMode = False
  
  Range("J2").Copy 'Contains 1
With Columns("D:D")
  .PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide, SkipBlanks:=True
  .NumberFormat = "dd/mm/yy"
End With
  
End Sub

I can't get your other options to behave any differently whether I use the manual method or the VBA method.
Depending on why its being treated as text, it sometimes works and sometimes doesn't.

Here is the code to only convert the text ones for all the nominated columns.

VBA Code:
Sub convertDates()
    
    Application.ScreenUpdating = False
    
    Dim srcRng As Range
    Dim RowStart As Long
    Dim RowLast As Long
    Dim iRow As Long
    Dim sht As Worksheet
    Dim arrCol As Variant
    Dim strColList As String
    Dim cnt As Long
    
    
    On Error Resume Next
    Set sht = ActiveSheet
    strColList = "B,D,L,M,X"
    arrCol = Split(strColList, ",")
    
    RowStart = 2
    RowLast = sht.Cells(sht.Rows.Count, "M").End(xlUp).Row
    
    For cnt = LBound(arrCol) To UBound(arrCol)
        RowStart = 2
        RowLast = sht.Cells(sht.Rows.Count, arrCol(cnt)).End(xlUp).Row
        
        For iRow = RowStart To RowLast
            If Not IsNumeric(Cells(iRow, arrCol(cnt)).Value2) Then
                sht.Cells(iRow, arrCol(cnt)) = CDate(sht.Cells(iRow, arrCol(cnt)))
            End If
        Next iRow
    Next cnt
    
    On Error GoTo 0
    
    Application.ScreenUpdating = True
           
End Sub
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

rhombus4

Active Member
Joined
May 26, 2010
Messages
422
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks, the code for the single one and the one with arrays worked but both are really slow as some of the columns have up to 10,0000 rows of data

You mentioned none of the manual methods worked.

So if you have a column of dates formatted as text. Highlight that column and then Replace and Find with a forward slash / it doesn't convert to a real date. All the manual ones worked for me
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
607
Office Version
  1. 365
Platform
  1. Windows
Unfortunately I don't have any of your data to work with, The usual method of giving me a sample via XL2BB would introduce yet another layer of Text to Numbers conversion.
Whether it works or not depends on what is making it appear as text in the first place, since I have had to manually create the data I have had to use different methods to force the data to be text, some convert some don't. I have had one clear difference using divide by 1 manually converted '10/12/2030 to 10/12/2030 but running divide 1 as a macro changed it to 12/10/2030. This seems to be the case for anything I have forced as text by adding ' (single quote). If I add a space it fails to convert without unless you convert by removing the spaces.
(Note: the Cdate macro seems to be consistent with the Manual divide + it will handle the ones with spaces as well)

I am worried that you are trying to automate a process that is fundamentally flawed. Its one thing to do this as a one off process but a macro implies you are planning to use this repeatedly.
(unless its just an exercise in which case its fine).

The process is likely to follow something along these lines:

1615588274689.png


Its seems highly likely that the numeric dates in Workbook A, that you believe to be dd/mm are in fact mm/dd.
The number of text dates indicates the Excel has already attempted to interpret the dates and these have failed to convert.
If this is going to be a recurring automated process you really need to start at the steps prior to Workbook A.
 

rhombus4

Active Member
Joined
May 26, 2010
Messages
422
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
The text dates in workbook A are dd/mm. When I filter them they appear as 01/02/20, 01/03/20 etc. When I manually replace and find with / or any of the other 3 methods and then filter them they are filtered by group i.e. 2018 , 2019, 2020

First part of code copies all the data from workbook A as values and formats. Then the part I need is to convert the dates formatted as text to be dates

You mentioned If I add a space. That is the paste special operation add, which technically adds a 0 which forces it to recalculate the text and turns it to numbers. I think all 4 methods do the same it similar.
 

Alex Blakenburg

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

ADVERTISEMENT

If you want to try this and see if it runs any faster.
Its essentially doing what you started with in the first place just using an array.
It still won't be as fast as the PasteSpecial options but as your and my testing indicated the PasteSpecial macros don't seem to work in a consistent manner when compared to the manual method.

VBA Code:
Sub convertDatesUsingArr()
    
    Application.ScreenUpdating = False
    
    Dim srcRng As Range
    Dim RowStart As Long
    Dim RowLast As Long
    Dim iRow As Long
    Dim sht As Worksheet
    Dim arrCol As Variant
    Dim strColList As String
    Dim cnt As Long
    Dim arrDT As Variant
    Dim iRowLast As Long
    
    
    On Error Resume Next
    Set sht = ActiveSheet
    strColList = "B,D,L,M,X"
    arrCol = Split(strColList, ",")
    
    RowStart = 2
    RowLast = sht.Cells(sht.Rows.Count, "M").End(xlUp).Row
    
    For cnt = LBound(arrCol) To UBound(arrCol)
        RowStart = 2
        RowLast = sht.Cells(sht.Rows.Count, arrCol(cnt)).End(xlUp).Row
        
        Set srcRng = sht.Range(Cells(RowStart, arrCol(cnt)), sht.Cells(RowLast, arrCol(cnt)))
        arrDT = srcRng
        
        iRowLast = RowLast - RowStart + 1
        
        For iRow = 1 To iRowLast
            arrDT(iRow, 1) = CDate(arrDT(iRow, 1))
        Next iRow
        
        'Write Back the converted array
        srcRng = arrDT
    Next cnt
    
    On Error GoTo 0
    
    Application.ScreenUpdating = True
           
End Sub
 

rhombus4

Active Member
Joined
May 26, 2010
Messages
422
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Seems alot quicker, however if there are any blanks it fills them in as 00/01/1900 also if there is an incorrect entry i.e. 1/2/123 it seems to stop converting at that point
 

Alex Blakenburg

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

ADVERTISEMENT

What do you want to see if it's invalid ?
The invalid value ?
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,303
Office Version
  1. 2010
Platform
  1. Windows
00/01/1900 = "0" when converted to date format
it appears looking through the discussion that the raw data is not available to work from. this is by far the best way to resolve the bulk of the problems here by the look. excel plays tricks with dates. if you are able to work from the original source, then keep the whole lot as text and interpret it from the text. much more reliable result
 

rhombus4

Active Member
Joined
May 26, 2010
Messages
422
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
If an entry is invalid then "error" and it can be dealt with later. If blank then ""

Was thinking about using Barrys code in post #2 which seemed to work when first converting the code it was only when it was run again that it flipped the day and month

So maybe something like
If value in column m is text then convert. If already a proper date the no nothing, but wasn't sure how to add that

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

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
607
Office Version
  1. 365
Platform
  1. Windows
Seems alot quicker, however if there are any blanks it fills them in as 00/01/1900 also if there is an incorrect entry i.e. 1/2/123 it seems to stop converting at that point
Using the faster array version won't let me substitute values for the invalid dates.
The version below should leave blanks as blanks and will output the invalid dates as text just the way they were and solve the issue of it not converting anything after it hits an invalid date.
IsDate did not trap "1/2/123" so I have put a limit on years being in the range of 1950-2050 and it will accept any 2 digit year.

VBA Code:
Sub convertDatesUsingArr()
    
    Application.ScreenUpdating = False
    
    Dim srcRng As Range
    Dim RowStart As Long
    Dim RowLast As Long
    Dim iRow As Long
    Dim sht As Worksheet
    Dim arrCol As Variant
    Dim strColList As String
    Dim cnt As Long
    Dim arrDT As Variant
    Dim dtSplit As Variant

    Dim iRowLast As Long
    
    
    On Error Resume Next
    Set sht = ActiveSheet
    'strColList = "B,D,L,M,X"
    strColList = "M"
    arrCol = Split(strColList, ",")
    
    RowStart = 2
    RowLast = sht.Cells(sht.Rows.Count, "M").End(xlUp).Row
    
    For cnt = LBound(arrCol) To UBound(arrCol)
        RowStart = 2
        RowLast = sht.Cells(sht.Rows.Count, arrCol(cnt)).End(xlUp).Row
        
        Set srcRng = sht.Range(Cells(RowStart, arrCol(cnt)), sht.Cells(RowLast, arrCol(cnt)))
        arrDT = srcRng
        
        iRowLast = RowLast - RowStart + 1
        
        For iRow = 1 To iRowLast
            dtSplit = Split(arrDT(iRow, 1), "/")
            If UBound(dtSplit) = 2 Then
                If (Val(dtSplit(2)) > 1950 And Val(dtSplit(2)) < 2050) _
                    Or Len(dtSplit(2)) = 2 Then
                        If IsDate(arrDT(iRow, 1)) Then
                            On Error Resume Next
                            arrDT(iRow, 1) = CDate(arrDT(iRow, 1))
                        End If
                End If
            End If
        Next iRow
        
        'Write Back the converted array
        srcRng.Value = arrDT
        
    Next cnt
    
    On Error GoTo 0
    
    Application.ScreenUpdating = True
              
End Sub
 
Solution

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