Validating date format

Solution 1:

Function dateCheck(dateStrng As String) As Boolean
Dim dateArr as Variant 

If IsDate(dateStrng) Then ' <~~ if it IS a date
   dateArr = Split(dateStrng,"/")
   If UBound(dateArr) = 2 Then '<~~ if it has three substrings separate by two "slashes"
      If CInt(dateArr(0)) < 13 Then  '<~~ if the 1st number is lower or equals the maximum possible month number
         If CInt(dateArr(0)) > 0 Then  '<~~ if the 1st number is higher or equals the mimimum possible month number
            If CInt(dateArr(1)) < 31 Then  '<~~ if the 2nd number is lower or equals the maximum possible day number
               If CInt(dateArr(1)) > 0 Then  '<~~ if the 2nd number is higher or equals the mimimum possible day number
                  If CInt(dateArr(2)) < 10000 Then dateCheck = CInt(dateArr(2)) > 999  '<~~ if the 3rd number is a 4 digit integer "year" number, then check returns True
               End If
            End If
         End If
      End If
   End If
End Function

Solution 2:

The CDate(dateValue) part of your function will simply return 'dateValue' as a Date. Use the .Numberformat property to get the format:

Function dateCheck(dateValue As Date) As Boolean
    If dateValue.NumberFormat <> "mm/dd/yyyy" Then
        MsgBox "Please use the mm/dd/yyyy  date format!"
        dateCheck = True
    End If
End Function

Solution 3:

The problem is that your question is compounded. There are actually two steps involved to your question:

1. Is txtStartDate actually a valid date?
2. If it is a date, is it formatted correctly?

The naming of txtStartDate implies that you are getting a date as a text (in a form). Yet, you are passing it to your function with the assumption that txtStartDate is actually a date. This becomes obvious because the function dateCheck expects a date: Function dateCheck(dateValue As Date) As Boolean.

So, here is a solution proposal to solve both at once:

Sub tmpTest()

Dim txtStartDate As String

txtStartDate = "11/20/2015"

Debug.Print dateCheck(txtStartDate)

End Sub

Function dateCheck(dateValue As String) As Boolean

If IIf(IsDate(txtStartDate), Format(CDate(txtStartDate), "mm/dd/yyyy"), "") = dateValue Then dateCheck = True

End Function

Keep in mind that this is a very simplistic approach which will not work for international date formats. In fact, you might have to adjust it a bit as I do not have your date format. If you need something more sophisticated then you will have to write a bit more VBA code (including an extensive validation function).