CODEWITHSUNDEEP
vbadateexcel
Julian B
Julian B

Reputation: 532

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?

Upvotes: 2

Views: 3686

Answers (1)

Peter Albert
Peter Albert

Reputation: 17475

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.

Upvotes: 5

Related Questions