Hi Tom and Jim,
I've entered and tried the following in a blank worksheet.
The target cell format is dd/mmm/yy

Private Sub Worksheet_Change(ByVal Target As Range)
If IsDate(Target) = False Then
MsgBox "Not a valid Date"
End If
End Sub

Entry 30/02/03 - No message, cell display 30/02/03
Entry 28/02/03 - No message, cell display 28/Feb/2003
Entry 03/13/04 - No message, cell display 03/13/04
Entry 03/14/04 - No message, cell display 03/14/04
Entry 03/13/2004 - No message, cell display 03/13/2004
Entry 03/12/04 - No message, cell display 03/Dec/2004

ItThe same results apply using - as a separator.

Regards,
Don


"Tom Ogilvy" wrote in message
...
Don,
When you enter one of those dates, does the cell display a valid date. I
suspect yes it does. Therefore, it meets the definition that it could be
interpreted as a date.

Excel provides robust capabilities to do math with dates. So something
like
Date(2004,13,1) would be Jan 1, 2005. The number 20345 is a valid date
(Feb 3, 1963). There are many behaviors in Excel that don't match
people's expectations - but there is usually (not always) a reason the
behavior is that way.

--
Regards,
Tom Ogilvy

"Don Lloyd" wrote in message
...
Thank you Frank, DBAL and Norman

Apologies for quoting 1/100/04 as an untrapped value, which it isn't.

I am rather surprised by the fact that the IsDate Function does not
regard
for example, 30/02/04 and 03/13/04 as invalid dates. Even I know that !

While it is possible to employ workarounds (thank you for your
suggestions)
these are rather complex for what they achieve and in this particular
instance I will resort to using the Validation function. I don't like
the
imposed roadworks signs, which don't mean much to the average user, but
it
works and beggars can't be choosers.

I think the lesson to be learned is that those of us who are less well
informed should not implicity accept the claimed property of a function
as
infallible

Quote:
"IsDate returns True if the expression is a date or is recognizable as a
valid date; otherwise, it returns False."

The examples quoted (there are many others) are NOT dates, but the
function
returned True

Good, having got that off my chest I'm away to take it out on a golf
ball.
Watch out Tiger !

Regards,
Don



"Don Lloyd" wrote in message
...
Hi,

Excel 97, Excel 2003
cell format dd/mm/yy

Q.
In the Worksheet Change routine I have the following code

If IsDate(Target) = False Then
MsgBox
End If

Entries such as 32/10/04, 1/100/04 are quite happily accepted as
dates.
Advice gratefully appreciated.

If possible I would rather trap with code rather than using data
validation.

Thanks for any assistance

Don