Formatting Text Dates to Excel Usable Date Format

Question

I have an excel file with some dates in the format "06 March, 2016" which I want to convert to "d/MM/yyyy" or "6/3/2016" in order to use excel formulas like DATEVALUE() on it to extract parts of the date.

I wrote a small macro to help me with this which just replaces the dates as I would manually in the input dataset.

Sub MonthReplace()
    Dim res As Boolean
    Dim i As Long
    Dim monthArray As Variant

    monthArray = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October",     "November", "December")    

    For i = LBound(monthArray) To UBound(monthArray)
        res = Range("RawDataset").Replace(" " + monthArray(i) + ", ", "/" + Format(i + 1) + "/")
    Next i

End Sub

The result is an unstable dataset. Please see the images, before and after.

Some are getting converted correctly, while other are getting their month and day interchanged. This behavior does not occur when I used to replace the months without using the Macro. The default date format in Excel is set as per my desired format.

System regional settings for Date:

Answer 1

After trying various methods and finally settled on this one which converts 06 March, 2016 to 06-March-2016 making it usable in Excel (my main goal) by explicitly stating the Month so to avoid VBA date format issues.

Sub MonthReplace()
    Dim res As Boolean
    Dim i As Long
    Dim endRow As Long
    Dim columnArray As Variant

    ' only work on columns containing the dates
    columnArray = Array("W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AL", "AM")

    ' find the last cell with data in the current sheet
    endRow = ActiveCell.SpecialCells(xlLastCell).Row

    For i = LBound(columnArray) To UBound(columnArray)
        With Range(columnArray(i) & "3:" & columnArray(i) & endRow)
            res = .Replace(", ", "-")
            res = .Replace(" ", "-")
        End With
    Next i
End Sub

Also, building upon the answer by +Axel Richter by checking for errors in Cell.Value and ensuring that the last 4 characters are digits, I wrote the following. However, this method is very slow since each cell is checked. One could use the above strategy (selected columns in the range) to improve the speed.

Sub MonthReplace_slow()

    Dim i As Long
    Dim monthArray As Variant
    Dim c As Range
    Dim strDate As String

    monthArray = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")

    For Each c In Range("RawDataset")
        strDate = ""
        If Not IsError(c.Value) Then
        strDate = c.Value
            If IsNumeric(Right(strDate, 4)) Then
                strDate = Replace(strDate, " ", "")
                strDate = Replace(strDate, ",", "")
                For i = LBound(monthArray) To UBound(monthArray)
                    strDate = Replace(strDate, monthArray(i), "/" & (i + 1) & "/", , , vbTextCompare)
                    If IsDate(strDate) Then Exit For
                Next i
                If IsDate(strDate) Then c.Value = CDate(strDate)
            End If
        End If
    Next

End Sub

I didn't play with the other CDate approaches.

Answer 2

Maybe the direct CDate approach will work for you since your system and Excel language seems to be English. For me it will not work since my system don't know the English month names. So I must truely replace them with numbers:

Sub MonthReplace()

    Dim i As Long
    Dim monthArray As Variant
    Dim c As Range
    Dim strDate As String

    monthArray = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")

    For Each c In Range("RawDataset")
     strDate = Replace(c.Value, " ", "")
     strDate = Replace(strDate, ",", "")
     For i = LBound(monthArray) To UBound(monthArray)
      strDate = Replace(strDate, monthArray(i), "/" & (i + 1) & "/", , , vbTextCompare)
      If IsDate(strDate) Then Exit For
     Next i
     If IsDate(strDate) Then c.Value = CDate(strDate)
    Next

End Sub

Answer 3

Converting each cell might be more reliable:

For Each Row In Range("RawDataset").Rows
  Row.Cells(1, 2) = CDate(Row.Cells(1, 1))
Next