[Excel] Valid Date Check
[Excel] Valid Date Check
(OP)
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
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





RE: [Excel] Valid Date Check
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 IsDate(), get back year, month and day from dtDate 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
RE: [Excel] Valid Date Check
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.