CODEWITHSUNDEEP
excelvba
rioZg
rioZg

Reputation: 580

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

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?

Upvotes: 0

Views: 92

Answers (2)

Gustav
Gustav

Reputation: 55831

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

Upvotes: 0

FunThomas
FunThomas

Reputation: 29286

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

Upvotes: 1

Related Questions