I'm working on a macro that looks at dates entered by users. I would like
to be able to require that those dates be real dates. I.e., no June 31. I
know I can enter such a date and XL doesn't question it. Is there any
built-in capability to give an error on such dates, or would I have to
write code and use some kind of lookup to check the validity of each date?
Thanks!

If you take the value of each cell that contains a date, whether or not it
truly is a correct date, and try to add 1 to it, you'll get an error if it is
not a date. Therefore, you just need an error catch, such as:

Sub checkDate()
..previous code...

On Error GoTo thisError

myDate = DateAdd("d", 1, ActiveCell.Value)
MsgBox myDate

...rest of code...
Exit Sub

thisError:
MsgBox "Error"
End Sub

Hope this helps, Jim

Hi,

Excel doesn't recognise 31/6/2008 as a date but isn't clever enough to do
anything about it, it simply accepts the entry but doesn't automatically
format as a date.

In Vb a date such as that would evaluate as FALSE if you used
IsDate(Range("A1"))

At the worksheet level the formula
=ISNUMBER(A1)
would evaluate as false for an invalid date.

Mike


"salgud" wrote:

I'm working on a macro that looks at dates entered by users. I would like
to be able to require that those dates be real dates. I.e., no June 31. I
know I can enter such a date and XL doesn't question it. Is there any
built-in capability to give an error on such dates, or would I have to
write code and use some kind of lookup to check the validity of each date?
Thanks!

Data Validation Allow - select Date then enter limits

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"salgud" wrote in message
.. .
I'm working on a macro that looks at dates entered by users. I would like
to be able to require that those dates be real dates. I.e., no June 31. I
know I can enter such a date and XL doesn't question it. Is there any
built-in capability to give an error on such dates, or would I have to
write code and use some kind of lookup to check the validity of each date?
Thanks!

On Wed, 30 Jul 2008 10:45:01 -0700, J Sedoff wrote:

If you take the value of each cell that contains a date, whether or not it
truly is a correct date, and try to add 1 to it, you'll get an error if it is
not a date. Therefore, you just need an error catch, such as:

Sub checkDate()
..previous code...

On Error GoTo thisError

myDate = DateAdd("d", 1, ActiveCell.Value)
MsgBox myDate

...rest of code...
Exit Sub

thisError:
MsgBox "Error"
End Sub

Hope this helps, Jim

Helps a lot! Thanks.

On Wed, 30 Jul 2008 10:46:00 -0700, Mike H wrote:

Hi,

Excel doesn't recognise 31/6/2008 as a date but isn't clever enough to do
anything about it, it simply accepts the entry but doesn't automatically
format as a date.

In Vb a date such as that would evaluate as FALSE if you used
IsDate(Range("A1"))

At the worksheet level the formula
=ISNUMBER(A1)
would evaluate as false for an invalid date.

Mike


"salgud" wrote:

I'm working on a macro that looks at dates entered by users. I would like
to be able to require that those dates be real dates. I.e., no June 31. I
know I can enter such a date and XL doesn't question it. Is there any
built-in capability to give an error on such dates, or would I have to
write code and use some kind of lookup to check the validity of each date?
Thanks!


Great! Thanks.

On Wed, 30 Jul 2008 18:50:44 +0100, Sandy Mann wrote:

Data Validation Allow - select Date then enter limits

Thanks.
Don't know how to use that. I'd have to enter the limits for each month
somehow. I think this is more for setting a date range for a given cell,
rather than only allowing valid dates for every month in the year.

Perhaps I have mislead you with saying limits. With the Start Date as
1/1/1900 and the end date as - say 31/12/2050 then it will object to any
entry that is not a valid date.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"salgud" wrote in message
...
On Wed, 30 Jul 2008 18:50:44 +0100, Sandy Mann wrote:

Data Validation Allow - select Date then enter limits

Thanks.
Don't know how to use that. I'd have to enter the limits for each month
somehow. I think this is more for setting a date range for a given cell,
rather than only allowing valid dates for every month in the year.

On Wed, 30 Jul 2008 19:11:00 +0100, Sandy Mann wrote:

Perhaps I have mislead you with saying limits. With the Start Date as
1/1/1900 and the end date as - say 31/12/2050 then it will object to any
entry that is not a valid date.

Oic. Thanks!

On Wed, 30 Jul 2008 19:11:00 +0100, "Sandy Mann"
wrote:

Perhaps I have mislead you with saying limits. With the Start Date as
1/1/1900 and the end date as - say 31/12/2050 then it will object to any
entry that is not a valid date.

--
HTH

Sandy

At least in 2007, it is more complicated than that. But I'm not sure what the
OP really wants.

For example, it is the case, given your limits, that 31/6 would be rejected, as
would 31/6/2008. But an entry, such as the OP posits, of June 31 would
accepted and interpreted as 1-June-2031. And, at least with US settings, 6/31
would also be accepted and interpreted at 1-June-2031.

The OP should make the range of acceptable dates as narrow as possible, but he
needs to be aware as to how Excel parses date entry.
--ron

Thnaks for pointing that out Ron, it never occurred to me to test with US
style dates before I suggested it. Even 10 years behind the times it does
the same.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Ron Rosenfeld" wrote in message
...
On Wed, 30 Jul 2008 19:11:00 +0100, "Sandy Mann"

wrote:

Perhaps I have mislead you with saying limits. With the Start Date as
1/1/1900 and the end date as - say 31/12/2050 then it will object to any
entry that is not a valid date.

--
HTH

Sandy

At least in 2007, it is more complicated than that. But I'm not sure what
the
OP really wants.

For example, it is the case, given your limits, that 31/6 would be
rejected, as
would 31/6/2008. But an entry, such as the OP posits, of June 31 would
accepted and interpreted as 1-June-2031. And, at least with US settings,
6/31
would also be accepted and interpreted at 1-June-2031.

The OP should make the range of acceptable dates as narrow as possible,
but he
needs to be aware as to how Excel parses date entry.
--ron

On Thu, 31 Jul 2008 11:11:12 +0100, "Sandy Mann"
wrote:

Thnaks for pointing that out Ron, it never occurred to me to test with US
style dates before I suggested it. Even 10 years behind the times it does
the same.

Yes, Excel's parsing of date entry can certainly be confusing at times. This
was the first I'd looked at the parsing on non-US date formats, though.

--ron