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
Can you click on the a date in the source workbook and then look at what formatting has been used in the workbook.
I think the original dates are dodgy. Did the other workbook originally come out of the US or use data imported from the US ?

Do you have anyway of validating the original dates ? ie if it is not showing as being text and shows 08/06/20 are you able to independently verify whether it should be June or August ?
Can you get the original data from which your source workbook was generated ?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

rhombus4

Active Member
Joined
May 26, 2010
Messages
422
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Source workbook is formatted the same dd/mm/yyyy. I use VBA to copy it to my workbook and then tried to convert dates formatted as text to dates

I can manually highlight column and do replace find using / or a space but wouldn't work using VBA
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
607
Office Version
  1. 365
Platform
  1. Windows
If the actual date is format applied in the formatting box (not what it looks like on screen) is dd/mm/yyyy and they have forced the some to be text presumably all the ones with day > 12, Its quite likely that all the days & months are reversed, apart from the ones manually modified to be text (assumigng these have been forced in somehow)
 

rhombus4

Active Member
Joined
May 26, 2010
Messages
422
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Not sure what you mean. I just need to convert a few columns of data. If it's a date formatted as text then convert to a date if it's already a date then leave as it is.
 

Alex Blakenburg

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

ADVERTISEMENT

Do you have any that are already a date where the value for the is day > 12 or do only the text ones have the day part > 12 ?
I am looking for some logic for the conversion.

O
 

rhombus4

Active Member
Joined
May 26, 2010
Messages
422
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
None of the first part (Day) is greater than 12

When I filter the Column initially all the filter appears as individual dates
i.e 01/02/20, 01/03/20, 01/04/20 , 01/05/20 etc. When its converted it usually is grouped i.e. 2019 2020 2021 etc

Code im using is below which will keep switching the first two parts every time its run
i.e. 01/02/20 will become 02/01/20 then back to 01/02/20 when it is run again

and If I change xlDMYFormat to xlMDYFormat it still switches every time it is run but wont change anything like 28/01/20

VBA Code:
Sub converttext()
        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

End Sub
 

Alex Blakenburg

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

ADVERTISEMENT

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

If you can find the 05/08/2020 line that swapped around and you use =value() on it, what number do you get.

20210312 Date US UK and Back Issue.xlsm
CDE
26CountryDateDate Value
27UK5/08/202044048
28UK8/05/202043959
29US08/05/202044048
30US05/08/202043959
Sheet1
Cell Formulas
RangeFormula
E27:E30E27=VALUE(D27)
 

rhombus4

Active Member
Joined
May 26, 2010
Messages
422
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
05/08/20 is 44048 and when it switches to 08/05/20 its 43959
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
607
Office Version
  1. 365
Platform
  1. Windows
Given the issues you are having and that you only want to convert the Text values you can try this but it may run just as slowly as what you started out with.
If you have formulas in the sheet, you could try adding
Application.Calculation = xlCalculationManual / Application.Calculation = xlCalculationAutomatic

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
    
    On Error Resume Next
    Set sht = ActiveSheet
    RowStart = 2
    RowLast = sht.Cells(sht.Rows.Count, "M").End(xlUp).Row
    
    For iRow = RowStart To RowLast
        If Not IsNumeric(Cells(iRow, "M").Value2) Then
            sht.Cells(iRow, "M") = CDate(sht.Cells(iRow, "M"))
        End If
    Next iRow
    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
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
 

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