Regular Expression to Test Date VBA

Question

I am looking for a code to test date Format, the date should be in one of these formats year : 13xx - 20xx month: xx,x day: xx,x the hole date would be on of the following 2012/1/1 2012/01/01 2012/1/01 2012/01/1

I tried the following

    Option Explicit
Sub ttt()
MsgBox (testDate("2012/01/01"))

End Sub

Function testDate(strDateToBeTested As String) As Boolean
Dim regularExpression, match
Set regularExpression = CreateObject("vbscript.regexp")
testDate = False
'regularExpression.Pattern = "(14|13|19|20)[0-9]{2}[- /.]([0-9]{1,2})[- /.]([0-9]{1,2})"
'regularExpression.Pattern = "(\d\d\d\d)/(\d|\d\d)/(\d|/dd)"
regularExpression.Pattern = "([0-9]{4}[ /](0[1-9]|[12][0-9]|3[01])[ /](0[1-9]|1[012]))"
regularExpression.Global = True
regularExpression.MultiLine = True

If regularExpression.Test(strDateToBeTested) Then

'    For Each match In regularExpression.Execute(strDateToBeTested)
      If Len(strDateToBeTested) < 10 Then
        testDate = True
'        Exit For
      End If
'End If
End If
Set regularExpression = Nothing
End Function

What is the error? and it would be helpful if you post expected output and inputs. — karthik manchala, Apr 08 '15 at 22:51
An excellent thread for validating date formats with regex was discussed in [Regex to validate date format dd/mm/yyyy](http://stackoverflow.com/questions/15491894/regex-to-validate-date-format-dd-mm-yyyy) — PeterT, Apr 08 '15 at 23:56
@karthikmanchala the problem is I am looking for Hijri calendars where 1435/02/30 is correct, the regex will accept 1435/2/2 but always refuses 1435/02/02 — Mohammad Awni Ali, Apr 10 '15 at 00:15

PeterT · Accepted Answer · 2015-04-10T15:00:49.843

The more and more I thought about this (and some research), the more I figured that regex is not the best solution to this format problem. Combining a couple of other ideas (with the ReplaceAndSplit function attributed to the owner), this is what I came up with.

Option Explicit

Sub ttt()
    Dim dateStr() As String
    Dim i  As Integer
    dateStr = Split("2012/1/1,2012/01/01,2012/1/01,2012/01/1,1435/2/2," & _
                    "1435/02/02,1900/07/07,1435/02/02222222,2015/Jan/03", ",")
    For i = 1 To UBound(dateStr)
        Debug.Print "trying '" & dateStr(i) & "' ... " & testDate(dateStr(i))
    Next i
End Sub

Function testDate(strDateToBeTested As String) As Boolean
    Dim dateParts() As String
    Dim y, m, d As Long
    dateParts = ReplaceAndSplit(strDateToBeTested, "/.-")
    testDate = False
    If IsNumeric(dateParts(0)) Then
        y = Int(dateParts(0))
    Else
        Exit Function
    End If
    If IsNumeric(dateParts(1)) Then
        m = Int(dateParts(1))
    Else
        Exit Function
    End If
    If IsNumeric(dateParts(2)) Then
        d = Int(dateParts(2))
    Else
        Exit Function
    End If
    If (y >= 1435) And (y < 2020) Then  'change or remove the upper limit as needed
        If (m >= 1) And (m <= 12) Then
            If (d >= 1) And (d <= 30) Then
                testDate = True
            End If
        End If
    End If
End Function

'=======================================================
'ReplaceAndSplit by alainbryden, optimized by aikimark
'Uses the native REPLACE() function to replace all delimiters with a common
'delimiter, and then splits them based on that.
'=======================================================
Function ReplaceAndSplit(ByRef Text As String, ByRef DelimChars As String) As String()
    Dim DelimLen As Long, Delim As Long
    Dim strTemp As String, Delim1 As String, Arr() As String, ThisDelim As String
    strTemp = Text
    Delim1 = Left$(DelimChars, 1)
    DelimLen = Len(DelimChars)
    For Delim = 2 To DelimLen
        ThisDelim = Mid$(DelimChars, Delim, 1)
        If InStr(strTemp, ThisDelim) <> 0 Then _
            strTemp = Replace(strTemp, ThisDelim, Delim1)
    Next
    ReplaceAndSplit = Split(strTemp, Delim1)
End Function

I totally agree with you, but what I am testing is Hijri dates, where 1435/02/30 is correct date, therefore I wrote the regex, but it will always have a problem that the regex accepts 1435/2/2 but refuses 1435/02/02 and always it will accept 1435/02/02222222 — Mohammad Awni Ali, Apr 10 '15 at 00:13
This took some puzzling, but I believe my code edits above give you a working solution. Regex is not the way to go with this type of format checking. — PeterT, Apr 10 '15 at 15:01
thank you very much, the code you wrote is exactly what I am looking for :) you made my day pro — Mohammad Awni Ali, Apr 11 '15 at 14:25

Regular Expression to Test Date VBA

1 Answers1