PDA

View Full Version : [SOLVED] Bogus Dates



MWE
08-17-2005, 07:35 PM
is there a test in VBA for "valid dates" that actually works?

The IsDate function is supposed to return False if the arguement is not something that can be interpreted as a date, or is of the right format but the date is invalid, e.g., 31-Feb-2005 Unfortunately, IsDate thinks 31-Feb-2005 is OK:dunno

Emily
08-17-2005, 07:53 PM
IsDate("31-Feb -2005") --- False
IsDate("February 31, 2005") --- False
IsDate("Feb 31, 2005") --- False
IsDate(#2/31/2005#) --- VBA error occur

MWE
08-17-2005, 08:07 PM
IsDate("31-Feb -2005") --- False
IsDate("February 31, 2005") --- False
IsDate(#2/31/2005#) --- VBA error occur

Thanks (I think). Actually, I do not know how to respond to this ... IsDate was NOT working correctly 15 minutes ago and is now ???

This is the little test proc I wrote. 15 minutes ago, entering 31-Feb-2005 yielded a true. Now (have rebooted once) it triggers an error and exits (as it should).



Sub Test_IsDate()
Dim ThisDate As Date
GetDate:
On Error GoTo ExitSub
ThisDate = InputBox("enter date")
MsgBox IsDate(ThisDate)
GoTo GetDate
ExitSub:
End Sub

this must be some of that Curious Macro Anomolies stuff with which kpuls often deals.:banghead:

Bob Phillips
08-18-2005, 03:28 AM
Perhaps you tried something like


isdate(dateserial(2005,2,31))

which returns True, not because of any problems in IsDate, but because DateSerial determines 31 days into Feb, or 3rd March, not 32st day of Feb.

Wolfgang
08-18-2005, 05:26 AM
which returns True, not because of any problems in IsDate, but because DateSerial determines 31 days into Feb, or 3rd March, not 32st day of February


hi xld,
would you please explain that statement to me? !

thank you very much,
wolfgang

Bob Phillips
08-18-2005, 07:05 AM
which returns True, not because of any problems in IsDate, but because DateSerial determines 31 days into Feb, or 3rd March, not 32st day of February


hi xld,
would you please explain that statement to me? !

That was my explanation.:devil:

What I mean is that DaterSerial in VBA is forgiving, as is Date in Excel. You pass it a year, a month and a number of days. So, logically if not physically it does
- get the number of years and start the date as that many years past 01/01/1900
- get the number of months and add that to our date so far (which if > 12 will increase the year part)
- get the number of days and add that to the date so far, which if > days in the month so far in our date, could increase the month, which in turn could increase the year.

Try this in the immediate window
?dateserial(2000,36,10)
?dateserial(2000,12,100)
?dateserial(1918,1,32000)

you should get the picture

.

MWE
08-18-2005, 08:32 AM
Perhaps you tried something like

isdate(dateserial(2005,2,31))

which returns True, not because of any problems in IsDate, but because DateSerial determines 31 days into Feb, or 3rd March, not 32st day of Feb.
no, I was not playing with serial dates (at least explicitly). The target application was having trouble with "bogus dates" and I wanted to use IsDate to ensure that bogus dates were trapped. Initially the test proc I wrote gave incorrect results. After I rebooted (for other reasons), the test proc gave correct results.

I found a different way to trap the problem dates, but I am still bothered by the fact that the test proc was yielding anomolous results.

Wolfgang
08-19-2005, 12:18 AM
hi xld,

thank's a lot for your quick reply and i "got the picture"....

best,
wolfgang