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

Hi
quite simple :-)
Excel thinks these are valid dates (and somehow is correct with
that...). You can test this if you use
msgbox datevalue(target.value)

so for example:
32/10/04: interepreted as YY/MM/DD and that is 04-Oct-1932

Your second example returns FALSE for me (but this could be something
with regional settings). Try this value with DateValue. what does it
return.

In total you can't do much against it if you use ISDATE. You could
check the individual parts of your entered string


--
Regards
Frank Kabel
Frankfurt, Germany


Don Lloyd wrote:
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

Don, this will force the user to enter a date in the
following format only MM/DD/YYYY. Here you go buddy:

'Isdate Perfected for Date Format MM/DD/YYYY by DBAL
'Get Date From User
Do
varDate = InputBox(prompt:="Enter the Date
(MM/DD/YYYY).", Title:="DATE")

If varDate = "" Then
Exit Sub

ElseIf Not IsDate(varDate) Then
MsgBox "You Must Enter A Valid Start Date"

ElseIf Len(varDate) < 10 Or Left(varDate, 2) 12
Or Mid(varDate, 4, 2) 31 Then
MsgBox "Your Entry Was " & varDate & "." & Chr
(13) & " Please Use This Date Format: (MM/DD/YYYY)."

End If
Loop Until IsDate(varDate) = True And Len(varDate) =
10 And Left(varDate, 2) <= 12 And Mid(varDate, 4, 2) <= 31


MsgBox "This Date Is Of The Proper Format
(MM/DD/YYYY): " & varDate



Best Regards,
DBAL
kungfoocomputers.com




-----Original 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


.

Hi Don,

You could adapt the Worksheet Change routine using information returned by
the Worksheet Day function, which does not behave in the same way as the
corresponding VBA function.

The following event procedure temporarily seconds a specified cell - empty
or not - to interrogate the target cell. In the code, this cell is set to
A1. You can change this to any cell which is NOT to receive a date entry:

I have only tested this with your specified non-US date formats.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Rng As Range
Dim OldVal As Variant

Set Rng = Range("A1") '<<< CHANGE TO SUIT
OldVal = Rng.Formula
With Application
.ScreenUpdating = False
End With

On Error GoTo CleanUp

If Intersect(Rng, Target) Is Nothing Then
Application.EnableEvents = False
Rng.Formula = "=Day(" & Target.Address & ")"
If IsError(Rng.Value) Then
MsgBox "Invalid date!"
End If
Rng.Formula = OldVal
Else
Exit Sub
End If

CleanUp:
Rng.Formula = OldVal
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
---
Regards,
Norman



"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

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

Don,

Are your sure that those non-dates are not being seen as "time"?
(values less than one)

Regards,
Jim Cone
San Francisco, CA

- snip -

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

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

Hi Don
but this result is as expected. Your invalid dates are all recognized
as 'strings'. Otherwise you would see the mont name. So the function
=ISNUMBER(cell_reference)
should return FALSE for these values (and ISTEXT(...) TRUE)

--
Regards
Frank Kabel
Frankfurt, Germany


Don Lloyd wrote:
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

Don,

I assume you want to limit the user entry to certain cells and
also to expect a date entry within a certain time span.
(the component parts are due sometime this year not ten years from now)

The following only checks the entry in 6 cells in column B and the date must be
within plus or minus approx. 6 months. Maybe something similar will
work for you...
'---------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("B5:B10")) Is Nothing Then
If Not IsDate(Target) Or _
Target.Value < (Date - 180) Or _
Target.Value (Date + 180) Then
MsgBox "Not a valid Date"
End If
End If
End Sub
'-------------------------------------------------------
Regards,
Jim Cone
San Francisco, CA

"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

- snip -

? 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

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