VBA Regular Expression to Match Date

Question

I'm new to Regular Expressions and am having difficulty getting patterns that I find online to work in VBScript/VBA. This one is supposed to return a date found in a string but it fails to find any dates. What does VBScript/VBA do different than other RegEx engines that makes this fail to return a match?

Edit1
I removed the ^ and the $ from my pattern. The problem persists.

Private Sub TestDate()
    MsgBox RegExDate("cancel on 12/21/2010 ")
End Sub

Private Function RegExDate(s As String) As String
    Dim re, match
    Set re = CreateObject("vbscript.regexp")
    re.Pattern = "(((0[1-9]|[12]\d|3[01])\/(0[13578]|1[02])\/((19|[2-9]\d)\d{2}))|((0[1-9]|[12]\d|30)\/(0[13456789]|1[012])\/((19|[2-9]\d)\d{2}))|((0[1-9]|1\d|2[0-8])\/02\/((19|[2-9]\d)\d{2}))|(29\/02\/((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))))"
    re.Global = True

    For Each match In re.Execute(s)
        MsgBox match.value
        RegExDate = match.value
        Exit For
    Next
    Set re = Nothing
End Function

ipr101 · Accepted Answer · 2011-10-28T14:04:22.077

7

It looks as if your RegEx will only find match if the whole string you pass to it is a date.

Try removing ^ and $

Here's your example reworked using a RegEx that will find dates in the mm/dd/yyyy and mm-dd-yyyy formats -

Private Sub TestDate()
    MsgBox RegExDate("cancel on 12/21/2010 ")
End Sub

Private Function RegExDate(s As String) As String
    Dim re, match
    Set re = CreateObject("vbscript.regexp")
    re.Pattern = "(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)[0-9]{2}"
    re.Global = True

    For Each match In re.Execute(s)
        MsgBox match.Value
        RegExDate = match.Value
        Exit For
    Next
    Set re = Nothing
End Function

edited Oct 28 '11 at 14:04

answered Oct 28 '11 at 12:27

ipr101

24,096
8
59
61

Great suggestion but that doesn't fix the problem. – HK1 Oct 28 '11 at 12:29
I've updated the answer with a new RegEx in your code sample, not sure if it's too specific though? – ipr101 Oct 28 '11 at 12:34
1

Any explanation on the changes that were needed? Also any ideas on how to make this find (mm/dd/yyyy) or (mm-dd-yyyy)? – HK1 Oct 28 '11 at 13:34
It will already find mm-dd-yyyy, sorry should have put that in the last comment – ipr101 Oct 28 '11 at 13:38
be aware, that will also match invalid dates eg. Feb 31st – macf00bar Oct 28 '11 at 13:56

Rob Haupt · Answer 2 · 2011-10-28T13:35:38.020

6

Why not use RegEx to get the portion of the string that appears to be the date and use the IsDate Function to validate it?

Function FormatOutput(s)
    Dim re, match
    Set re = CreateObject("vbscript.regexp")
    re.Pattern = "[\d]+[\/-][\d]+[\/-][\d]+"
    re.Global = True

    For Each match In re.Execute(s)
        if IsDate(match.value) then
            FormatOutput = CDate(match.value)
            Exit For
        end if
    Next
    Set re = Nothing

End Function

The RegEx could be cleared up a bit, but it works for your current example.

edited Oct 28 '11 at 13:35

answered Oct 28 '11 at 12:40

Rob Haupt

2,104
1
15
24

Will this find dates that are separated by dashes? But yeah, testing the string returned is certainly OK in this case. – HK1 Oct 28 '11 at 13:33
Updated the Pattern. it will match on / or - for date separators. – Rob Haupt Oct 28 '11 at 13:36
Great answer! Always having trouble with regular expressions :-s – keun Mar 28 '14 at 14:48

VBA Regular Expression to Match Date

2 Answers2

Linked