excel - 在某些 Excel 版本中无法使用 VBA 更改日期格式

Question

在某些版本的 Excel 中，我有一半的日期格式为 2018 年 3 月 31 日，单元格格式为一般格式，另一半为日期格式和 2018 年 3 月 31 日。这些是从某个地方导出的，所以我无法更改。这些日期用于数据透视表。

我努力了

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

覆盖格式并将它们全部匹配到相同的格式，但在数据透视表中，上半部分始终显示 Mar/31/2018 而不是 03/31/2018。并且 2018 年 3 月 31 日日期左对齐，而另一半日期右对齐正确格式 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.

我希望能够将一般格式覆盖为正确的日期格式。

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