I found a bug

[Don Wiss]

In a production workbook (Excel 2002) I have the users enter the effective
date on a user form. I use the IsDate function to be sure it is valid. I
then place the date into a locked cell on a sheet. The IsDate function says
that 10/9/007 is a valid date. But on the sheet Excel treats it as a
string. So of course all calculations throughout turn to #ERROR. Does this
bug still exist in later versions?

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[OssieMac]

Hi Don,

Not a bug. IsDate only determines if an expression can be converted to a
date.
(See help under isdate)

Use something like this:- Range("A1") = DateValue(Yourdate)
where yourdate is a string expression.

Regards,

OssieMac

 

[Bernie Deitrick]

Don,

I don't know if that bug still exists in later versions, but the way to
handle that is to use DateValue:

Dim myS As String
myS = "10/9/007"

MsgBox IsDate(myS)
Cells(1, 1).Value = myS
Cells(2, 1).Value = DateValue(myS)

HTH,
Bernie
MS Excel MVP