Checking for invalid dates

  • Thread starter Thread starter Matt Pinto
  • Start date Start date
M

Matt Pinto

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
 
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
Click to expand...

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
 
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
Click to expand...

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
 
Back
Top