Very Simple Date Issue

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Somebody please help me here. Why does this function NOT work? IsDate
returns false, even though it is a valid date.

Function main()
Dim a, b, c, sDate

sDate = "20011130"
a = Left(sDate, 4)
b = Mid(sDate, 5, 2)
c = Right(sDate, 2)

If IsDate(a & b & c) Then
main = DateSerial(a, b, c)
Else
main = DateSerial(2111, 1, 1)
End If

End Function

Thanks.
 
Hi Craig,

In your code, the expression
a & b & c
returns the string
20011130
which is what you started with. Try
a & "-" & b & "-" & c
instead.
 
You need to convert the a & b & c value to a date first. Either by using
DateSerial(a, b, c) in the If..then or using CVDate().

If IsDate(DateSerial(a, b, c))
 
I think John's suggestion is better, David.

Let's assume that sDate is 20053040. While that's not a valid date, your
expression will return True:

?IsDate(DateSerial(2005, 30, 40))
True

That's because DateSerial is too accomodating:

?DateSerial(2005, 30 , 40)
2007-07-10

John's suggestion, however, will reject it:

?IsDate("2005-30-40")
False
 
Thanks for all the help!
--
Craig


Douglas J. Steele said:
I think John's suggestion is better, David.

Let's assume that sDate is 20053040. While that's not a valid date, your
expression will return True:

?IsDate(DateSerial(2005, 30, 40))
True

That's because DateSerial is too accomodating:

?DateSerial(2005, 30 , 40)
2007-07-10

John's suggestion, however, will reject it:

?IsDate("2005-30-40")
False
Click to expand...
 
Back
Top