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

 

[Alex Blakenburg]

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 ?

 

[rhombus4]

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]

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]

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]

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]

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

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]

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
	C	D	E
26	Country	Date	Date Value
27	UK	5/08/2020	44048
28	UK	8/05/2020	43959
29	US	08/05/2020	44048
30	US	05/08/2020	43959
Sheet1

Cell Formulas
Range	Formula
E27:E30	E27	=VALUE(D27)

 

[rhombus4]

05/08/20 is 44048 and when it switches to 08/05/20 its 43959

 

[Alex Blakenburg]

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

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]

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

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