获取在excel中各种格式的日期单元格的年月部分 （VBA代码）

1. 公共部分   --> 开始按钮

Private Sub CommandButton1_Click()
    Err.Clear
    On Error Resume Next
    
    Dim aryALLData, colNames
    Dim curRowNo, curColNo, iRowCount, iColCount
    
    Const cnst_fldRowNo = 1
    Const cnst_BeginDataRowNo = 2
        
    '----------
    If MsgBox("开始执行吗 ?", vbYesNo, "确认开始") <> vbYes Then Exit Sub
        
    iColCount = UsedRange.Columns.Count
    
    aryALLData = UsedRange
    
    iRowCount = UBound(aryALLData, 1)
    iColCount = UBound(aryALLData, 2)
                
    '--- 获取 全部列名
    Set colNames = CreateObject("scripting.dictionary")
    For curColNo = 1 To iColCount
        If aryALLData(cnst_fldRowNo, curColNo) <> "" Then
            colNames.Add aryALLData(cnst_fldRowNo, curColNo), curColNo
        Else
            MsgBox "第" & curColNo & "列标题是空，无法继续"
            Exit Sub
        End If
        
    Next curColNo
            
    For curRowNo = cnst_BeginDataRowNo To iRowCount
        
        Cells(curRowNo, colNames("开始1")).Value = GetFormatedValue3_YYYYMM(CStr(aryALLData(curRowNo, colNames("开始日期"))))
        Cells(curRowNo, colNames("结束1")).Value = GetFormatedValue3_YYYYMM(CStr(aryALLData(curRowNo, colNames("结束日期"))))
        
    Next curRowNo
    
End Sub

1.  日期自动修正为 YYYY-MM格式的代码

2. Function GetFormatedValue3_YYYYMM(strInputDate As String) As String
       strInputDate = Trim(CStr(strInputDate))
   
       If InStr(1, strInputDate, "在岗") + InStr(1, strInputDate, "在职") > 0 Then
           GetFormatedValue3_YYYYMM = strInputDate
           Exit Function
       ElseIf Not (IsNumeric(Left(strInputDate, 4))) Then
           GetFormatedValue3_YYYYMM = "有误：" & strInputDate
           Exit Function
       End If
       
       Dim intYear, intMonth As Integer
       Dim FirstFindedPos As Long
       
       If IsDate(Left(strInputDate, 4) & "-" & Mid(strInputDate, 5, 2)) Then
           intYear = Left(strInputDate, 4)
           intMonth = Mid(strInputDate, 5, 2)
       Else
           
           '--- 1. 删除无需的
           strInputDate = Replace(strInputDate, "份", "")
           strInputDate = Replace(strInputDate, "日", "")
           
           '--- 2. 统一分隔符 -
           strInputDate = Replace(strInputDate, "-年", "-")
           strInputDate = Replace(strInputDate, "-月", "-")
           strInputDate = Replace(strInputDate, "年", "-")
           strInputDate = Replace(strInputDate, "月", "-")
           
           strInputDate = Replace(strInputDate, "/", "-")
           strInputDate = Replace(strInputDate, ".", "-")
           
               
           If InStr(1, strInputDate, "-") > 0 Then
               '--- 格式 ==-> 【-】
               FirstFindedPos = InStr(1, strInputDate, "-")
               intYear = Left(strInputDate, InStr(1, strInputDate, "-") - 1)
               
               If InStr(FirstFindedPos + 1, strInputDate, "-") = 0 Then
               'xxxx-mm
                   intMonth = Mid(strInputDate, FirstFindedPos + 1, 2)
               Else
                  intMonth = Mid(strInputDate, FirstFindedPos + 1, InStr(FirstFindedPos + 1, strInputDate, "-") - InStr(1, strInputDate, "-") - 1)
               End If
           End If
       
       End If
       
       If IsDate(intYear & "-" & intMonth) Then GetFormatedValue3_YYYYMM = "'" & Format(intYear & "-" & intMonth, "yyyy-mm")
       
   End Function