Thank you Tom for your patience and time in explaining the reasons behind
the problem.
I'm based in the UK - Wales in fact - which is as good a reaon as any for
using UK formats !

I trust that other persons frequenting this newsgroup who are not using US
formats will take note.

It therefore appears that I must either
a) Employ Excel's strongest Validation procedure or
b) more likely, use an error trapping routine when the invalidity is
recognised.

Once again, thanks for all your help.

Regards,
Don


"Tom Ogilvy" wrote in message
...
and as I showed you, VBA can interpret these as valid date - perhaps not
the
date intended, but it successfully interpreted each one of these. Some
you
are seeing as invalid simply because they are in a US format. However,
VBA
works in a US format. It will interpret the date as dd/mm/yy only if it
is
unsuccessful treating it as US. Also, from the tests, it is clear that
some
invalid entries in the dd portion makes it interpret it as yy/mm/dd. I
only
restate the above, because you appear to be missing the point that the
function is working exactly as the explanation you quote states. You seem
to want it to see dates the way you see them, but unfortunately that is
not
the case.

How excel itself works and how VBA works are two different situations. In
many cases, Excel works from one set of rules and VBA from another.
Sometimes they are close and sometimes not. VBA is a separate product and
has been Wed to Excel (and powerpoint and word and access and autocad,
etc).
But they are all developed by different groups and are not totally
consistent on the rules they use.


Another related example is if you import a CSV file with dates in dd/mm/yy
format in your version of excel, they will come in fine. If you record a
macro at the same time and run the macro, the dates will be screwed up
because of the US centric nature of VBA. This is true in Excel 97 and
2000
for sure.

--
Regards,
Tom Ogilvy


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

Thanks for your feedback and apologies for dragging the thread on for so
long.
It may help if I explain better what I am aiming for.

The user enters a date into a fixed cell - the program then generates a
list of up to about 40 consecutive weekly dates (depending on the value
of
another cell), starting with the original entry. This works perfectly if
a
valid date is entered.

I set up the IsDate function system some time ago and it appeared to
work
well, until I as a matter of routine, it was tested with some invalid
dates.
It came as a surprise, to me, that it failed to detect these.:
Quote from help
"IsDate Function


Returns a Boolean value indicating whether an expression can be
converted
to
a date.

Syntax

IsDate(expression)

The required expression argument is a Variant containing a date
expression
or string expression recognizable as a date or time " Unquote

From the cell display I would think that Excel would treat the date
entered
as string expression.

A further point - cell Validation instantly recognises the example dates
as
being invalid. However (I need to check further on this) Validation
allows
acceptance of an invalid entry in some cases, and the program will then
fail
on the next stage.

Thanks again for your help - I'll get there eventually !

Regards,
Don

"Tom Ogilvy" wrote in message
...
for the second one:

? isdate("28/02/03")
True
? datevalue("28/02/03")
2/3/28



"Tom Ogilvy" wrote in message
...
? isdate("30/02/03")
True
? datevalue("30/02/03")
2/3/30
? isdate("28/Feb/2003")
True
? dateValue("28/Feb/2003")
2/28/03
? isdate("03/13/04")
True
? datevalue("03/13/04")
3/13/04
? isdate("03/14/04")
True
? datevalue("03/14/04")
3/14/04
? isdate("03/13/2004")
True
? datevalue("03/13/2004")
3/13/04
? isdate("03/12/04")
True
? datevalue("03/12/04")
3/12/04

They can all be interpreted as a date in VBA.

--
Regards,
Tom Ogilvy



"Don Lloyd" wrote in message
...
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