Cannot change date format with VBA in some Excel versions

Question

In some versions of Excel, I have half of the dates in the format Mar/31/2018 and the cell formatting is general and the other half are Date format and 03/31/2018. These are exported from somewhere so I cannot change that. These dates are used in a pivot table.

I have tried

Range("C2:C200").NumberFormat = "m/dd/yyyy"

to override the format and match them all to the same format, but in the pivot table it always shows Mar/31/2018 instead of 03/31/2018 for the top half. And the Mar/31/2018 date is left aligned while the other half is right aligned with the correct format 03/31/2018.

Range("C2:C200").Value.NumberFormat = "m/dd/yyyy" does not work either. 

For i = 2 to lastRow Step 1
dateString = Cells(i, 3).Value
Cells(i, 3).Value = DateValue(dateString) only works for the cells that are already in custom or date format and not for the general format cells.

I'd like to be able to override the general format to a proper date format.

Answer 1

Option Explicit

Sub Convert2Date()
    Dim iCt As Integer
    Dim lastRow As Long
    Dim dateStr As Variant
    Dim dateArr() As String
    Dim YearInt As Integer, MonInt As Integer, dayInt As Integer
    'Range("C2:C200").Value.NumberFormat = "m/dd/yyyy" 'does not work either.
    'For i = 2 To lastRow
        'dateString = Cells(i, 3).Value
        'Cells(i, 3).Value = DateValue(dateString) 'only works for the cells that are already in custom or date format and not for the general format cells.


    lastRow = Range("C1").SpecialCells(xlCellTypeLastCell).Row
    For iCt = 2 To lastRow
        dateStr = Cells(iCt, 3).Value
        If Not (dateStr = "") Then
            If IsDate(dateStr) Then
                Cells(iCt, 5) = "isdate = OK!"
                Cells(iCt, 4) = CDate(dateStr)
            Else
                'Cells(iCt, 4) = CDate(dateStr)
                dateArr = Split(dateStr, "/")
                MonInt = ConvertMonth(dateArr(0))
                dayInt = CInt(dateArr(1))
                YearInt = CInt(dateArr(2))
                Cells(iCt, 4).Value = DateSerial(YearInt, MonInt, dayInt)
                Cells(iCt, 5) = "CONVERTED"
                Cells(iCt, 5).Interior.Color = vbYellow
            End If
        End If
    Next iCt
End Sub

Function ConvertMonth(MonthStr As String) As Integer
    Dim tempStr As String
    Dim tempInt As Integer
    tempStr = LCase(MonthStr)
    Select Case tempStr
        Case "jan"
            tempInt = 1
        Case "feb"
            tempInt = 2
        Case "mar"
            tempInt = 3
        Case "apr"
            tempInt = 4
        Case "may"
            tempInt = 5
        Case "jun"
            tempInt = 6
        Case "jul"
            tempInt = 7
        Case "aug"
            tempInt = 8
        Case "sep"
            tempInt = 9
        Case "oct"
            tempInt = 10
        Case "nov"
            tempInt = 11
        Case "dec"
            tempInt = 12
        Case Else
            Debug.Print "undefined month string"
            tempInt = 0
    End Select
    ConvertMonth = tempInt '(added, corrected in June 2022)
End Function

Answer 2

It is possible that some of your "dates" are actually Text values. To convert them to a common "real" format. Select the cells and run this:

Sub DateUnifier()
    Dim r As Range, d As Date, s As String, nf As String, arry
    nf = "m/d/yyyy"
    For Each r In Selection
        s = r.Text
        If s <> "" Then
            arry = Split(s, "/")
            If UBound(arry) = 2 Then
                If IsNumeric(arry(0)) Then
                    r.Clear
                    r.Value = DateValue(s)
                    r.NumberFormat = nf
                Else
                    r.Clear
                    r.Value = DateSerial(CInt(arry(2)), konvert(arry(0)), CInt(arry(1)))
                    r.NumberFormat = nf
                End If
            End If
        End If
    Next r
End Sub

Public Function konvert(st As Variant) As Integer
    mnths = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    i = 1
    For Each mn In mnths
        If st = a Then
            konvert = i
            Exit Function
        End If
    Next mn
End Function

CORRECTION:

There are errors in the konvert() function, use this instead:

Public Function konvert(st As Variant) As Integer

    mnths = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    i = 1
    For Each mn In mnths
        If st = mn Then
            konvert = i
            Exit Function
        End If
        i = i + 1
    Next mn
End Function