Days in a month?

[salgud]

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!

 

[J Sedoff]

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

 

[Mike H]

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

 

[Sandy Mann]

Data > Validation > Allow - select Date then enter limits

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[salgud]

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.

 

[salgud]

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

Great! Thanks.

 

[salgud]

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.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[salgud]

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!

 

[Ron Rosenfeld]

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.

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Ron Rosenfeld]

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