Formatting Text Dates to Excel Usable Date Format

0
votes

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.

BeforeAfter

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:

date settings

3
vbaexcel
The default short date format of your system seems to be m/d/yyyy and not d/m/yyyy. See 06 March. 2016 => 3/6/2016 and 02 March. 2016 => 3/2/2016. So 17 March. 2016 cannot be converted to date format from 17/3/2016 because month 17 is not known. – Axel Richter
@AxelRichter Added a screenshot of my date settings. Though what you say makes sense, any clue why the behavior of excel is different in the manual and macro based approaches? – Birla
Is there any reason for not using CDate to convert each string? – Florent B.
Oh, sorry, my fault. I have not thought about the VBA behavior. VBA will always be en_us with m/d/yy. So you must either set this format or use CDate("dd/mm/yyyy") to convert. – Axel Richter

3 Answers

1
votes

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
0
votes

Converting each cell might be more reliable:

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

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.