Hi folks,

I have come across an annoying problem in Excel.

I have a cell in an Excel 97 worksheet, with a format of
dd/mm/yy.

Before Close of the worksheet, I run a few validation
checks on various cells, including this date cell. It
appears that the IsDate function doesn't work when I have
a date of 40/01/03, yet it does work if I type in 40/01/94.

I can only imagine that Excel is reformatting the first
date and assuming that 40 is the year (when in fact it is
the day). Having reformatted it, it then treats it as a
valid date. My worry is that a user mistypes the date, and
it could potentially be treated as a valid date.

Is there any way of turning off this behaviour of trying
to interpret dates rather than taking them literally?


Cheers,

Matt

On Thu, 27 Nov 2003 07:57:18 -0800, "Matt Pinto"
wrote:

Hi folks,

I have come across an annoying problem in Excel.

I have a cell in an Excel 97 worksheet, with a format of
dd/mm/yy.

Before Close of the worksheet, I run a few validation
checks on various cells, including this date cell. It
appears that the IsDate function doesn't work when I have
a date of 40/01/03, yet it does work if I type in 40/01/94.

I can only imagine that Excel is reformatting the first
date and assuming that 40 is the year (when in fact it is
the day). Having reformatted it, it then treats it as a
valid date. My worry is that a user mistypes the date, and
it could potentially be treated as a valid date.

Is there any way of turning off this behaviour of trying
to interpret dates rather than taking them literally?


Cheers,

Matt



You could try using Data Validation.


--ron

Hi Ron,

Many thanks for that! It works a treat.
I didn't even know the Data Validation Menu Option was
there, and it will allow me to remove a lot of VBA code.

Once again, many thanks.

Matt



-----Original Message-----
On Thu, 27 Nov 2003 07:57:18 -0800, "Matt Pinto"
wrote:

Hi folks,

I have come across an annoying problem in Excel.

I have a cell in an Excel 97 worksheet, with a format of
dd/mm/yy.

Before Close of the worksheet, I run a few validation
checks on various cells, including this date cell. It
appears that the IsDate function doesn't work when I
have
a date of 40/01/03, yet it does work if I type in
40/01/94.

I can only imagine that Excel is reformatting the first
date and assuming that 40 is the year (when in fact it
is
the day). Having reformatted it, it then treats it as a
valid date. My worry is that a user mistypes the date,
and
it could potentially be treated as a valid date.

Is there any way of turning off this behaviour of trying
to interpret dates rather than taking them literally?


Cheers,

Matt



You could try using Data Validation.


--ron
.

On Fri, 28 Nov 2003 01:39:07 -0800, "Matt Pinto"
wrote:

Many thanks for that! It works a treat.
I didn't even know the Data Validation Menu Option was
there, and it will allow me to remove a lot of VBA code.

Once again, many thanks.

Matt

Glad to help.

Whatever algorithm is used in Data Validation seems to work better for dates
than the IsDATE VBA function.

You can also use Data Validation to cause messages to pop-up when the cell is
selected.


--ron