当前位置: 动力学知识库 > 问答 > 编程问答 >

excel - Letting VBA convert different date formats

问题描述:

I've got a VBA macro which reads Excel files and needs to process the data in there, including some dates. However, depending on the user who exported these files, the dates in those sheets might be written YYYYMMDD, MM/DD/YYYY, DD.MM.YYYY, M/D/YYYY and so on. All are only formatted as text.

So far, I've tried parsing the string and creating a new date. As I encounter more exotic dates, like M/DD/YYYY or D.MM.YY, my code gets very large and doesn't seem very elegant. I've searched, but I couldn't find any standardised way or function to detect these several date formats and convert them accordingly.

Am I missing something or is simply parsing the string the only reliable way for doing this?

网友答案:

Try this code - it'll convert any of the following formats: DD.MM.YYYY,DD.MM.YY,YYYYMMDD,MM/DD/YYYY,MM/DD/YY,M/D/YYYY, M/D/YY.

You can easily add additional formats, just add more conversion rules to the If...ElseIf... statement.

Option Explicit

Private mStrLastPattern As String
Private mStrSourceDate As String
Private mDatResult As Date

Public Function fctDateFromString(strDate As String) As Date
    mStrSourceDate = strDate
    mDatResult = 0
    If TryConvert("(^\d{2})\.(\d{2})\.(\d{4})$", "$2/$1/$3") Then       'DD.MM.YYYY
    ElseIf TryConvert("(^\d{2})\.(\d{2})\.(\d{2})$", "$2/$1/20$3") Then 'DD.MM.YY
    ElseIf TryConvert("(^\d{4})(\d{2})\.(\d{2})$", "$2/$3/$1") Then     'YYYYMMDD
    ElseIf TryConvert("(^\d{2})/(\d{2})/(\d{4})$", "$1/$2/$3") Then     'MM/DD/YYYY
    ElseIf TryConvert("(^\d{2})/(\d{2})/(\d{2})$", "$1/$2/20$3") Then   'MM/DD/YY
    ElseIf TryConvert("(^\d{1})/(\d{1})/(\d{4})$", "0$1/0$2/$3") Then   'M/D/YYYY
    ElseIf TryConvert("(^\d{1})/(\d{1})/(\d{2})$", "0$1/0$2/20$3") Then 'M/D/YY
    End If
    If mDatResult = 0 Then Debug.Print "Cannot find matching format for " & strDate
    fctDateFromString = mDatResult
End Function

Private Function TryConvert(strFrom As String, strTo As String) As Boolean
    If RegExMatch(strFrom) Then
        mDatResult = RegExConvert("$1/$2/$3")
        TryConvert = (mDatResult <> 0)
    End If
End Function

Private Function RegExMatch(strPattern As String) As Boolean
    mStrLastPattern = strPattern
    With CreateObject("VBScript.RegExp")
        .Pattern = strPattern
        .IgnoreCase = True
        .MultiLine = False
        RegExMatch = .Test(mStrSourceDate)
    End With
End Function

Private Function RegExConvert(strReplacePattern As String) As Date
    On Error Resume Next
    With CreateObject("VBScript.RegExp")
        .Pattern = mStrLastPattern
        .IgnoreCase = True
        .MultiLine = False
        RegExConvert = CDate(.Replace(mStrSourceDate, strReplacePattern))
        If Err.Number Then
            Err.Clear
            RegExConvert = 0
        End If
    End With

End Function

Note, that this code will interpret MM.DD.YYYY as DD.MM.YYYY and so on as long as the number of digits matches and the resulting date is valid.

分享给朋友:
您可能感兴趣的文章:
随机阅读: