Check if entered date in EXCEL VBA is date not working properly-CodePudding

Excel isDate returns true when I do not want it to be true.

For example

isDate(DateSerial(Month:=6, Day:=40, Year:=1970))

returns True

and inputing

DateSerial(Month:=6, Day:=40, Year:=1970))

as a value in a cell results in

10/7/1970

What I want is the user not to be able to enter 72nd of June but limit him to "real dates". I do not want Excel to compensate for it.

Is there a way?

CodePudding user response：

The dateSerial-function is implemented on purpose like that - it can be handy if you want to add a number of days or months.

What you could do is to write your own checking routine that gets day, month and year as parameter, pass this as parameter to DateSerial and check if day, month and year are equal to the parameter values you passed.

Function checkValidDate(dd As Long, mm As Long, yyyy As Long) As Boolean
    Dim tmpDate As Date
    On Error Resume Next
    tmpDate = DateSerial(yyyy, mm, dd)
    If Err.Number > 0 Then Exit Function
    On Error GoTo 0
    
    checkValidDate = (Day(tmpDate) = dd And Month(tmpDate) = mm And Year(tmpDate) = yyyy)
End Function


? checkValidDate(0, 0, 0)
False

? checkValidDate(10, 7, 1970)
True

? checkValidDate(40, 6, 1970)
False

CodePudding user response：

Extract and parse the text date and use IsDate:

PersonNummer = "1111010170"
If IsDate(Format(Right(PersonNummer, 6), "@@\/@@\/@@")) Then
    Debug.Print "OK"
Else
    Debug.Print "Not OK"
End If