[Excel] Valid Date Check

[Skullmonkey]

I'm receiving a date in three parts (month,day,year) from a userform. Is there a prescripted VBA function that can check to see if this is a valid date? I have been using

dtDate = CDate(DateSerial(iYear,iDay,iMonth))
bValidDate = IsDate(dtDate)

to make this decision, but

DateSerial

will accept negative numbers, zeros, and numbers outside of my range (i.e. "70" as

iDay

is not a problem). Also, I'd like to make sure that the year is entered as a four-digit number.

Can I avoid hard-coding this? Or does Excel offer any tools for this sort of thing?

Any input would be appreciated.

SM

 

[prex]

Don't see the perfect solution to your problem.
I propose two half way solutions (the second is preferred):
1)Transform your number to a string and use the date function that transforms it into a serial date: this function gives an error if day and month values are not valid, but accepts years in a two digits format.
2)Instead of checking the date with [tt]IsDate()[/tt], get back year, month and day from [tt]dtDate[/tt] with the corresponding functions and compare them with the input values: if they are the same then the input was OK. prex
motori@xcalcsREMOVE.com

http://www.xcalcs.com

Online tools for structural design

 

[dsi]

Maybe you could try something like this:

Dim sDate As String

sDate = Text1.Text & "/" & Text2.Text & "/" & Text3.Text
bValidDate = IsDate(sDate)

This removes the potential errors introduced by using the DateSerial method.

Hope this helps...

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.