I created the following function for the same situation.
Public Function ValidDate(DateStr As String, Sep As String) As Boolean
'The Function Checks a passed string for a valid date representation. Two digit years are considered invalid.
'Returns True or False
Dim MM As Variant, DD As Variant, YYYY As Variant, CkStr As String
If Len(Trim$(DateStr)) > 0 Then
Else
ValidDate = False
Exit Function
End If
CkStr = DateStr
'Break date string into it's parts
MM = Left(CkStr, InStr(CkStr, Sep) - 1)
CkStr = Mid$(CkStr, InStr(CkStr, Sep) + 1)
DD = Left(CkStr, InStr(CkStr, Sep) - 1)
CkStr = Mid$(CkStr, InStr(CkStr, Sep) + 1)
YYYY = Left(CkStr, Len(CkStr))
If IsNumeric(MM & DD & YYYY) Then
Else 'Check for Alpha in date
ValidDate = False
Exit Function
End If
'Year check-only 4 digit years allowed
If Len(Trim(CStr(YYYY))) <> 4 Then
ValidDate = False 'Not a 4 Digit Year
Exit Function
End If
'Test Month
If MM < 1 Or MM > 12 Then
ValidDate = False 'Invalid Month
Exit Function
End If
'Day check
If DD < 1 Or DD > 31 Then
ValidDate = False 'Invalid Day
Exit Function
End If
'Check Short Months
If (MM = 2 Or MM = 4 Or MM = 6 Or MM = 9 Or MM = 11) Then
If DD > 30 Then
ValidDate = False
Exit Function
End If
'Feb Day check
If MM = 2 Then
If DD > 28 Then
'Leap Year check
If DD = 29 Then
If YYYY Mod 4 <> 0 Then
ValidDate = False
Exit Function
End If
Else
ValidDate = False 'Date=Feb 30
Exit Function
End If
End If
End If
End If
'Date OK
ValidDate = True
End Function