Validating Dates

[Luis Verme]

I'm trying to validate dates whuen I make an input ("dd/mm/yy"). I use the
routine

If IsDate(txtFFinal) Then
MsgBox "Date is correct"
else
MsgBox "Date is correct"
End If

When I enter the date 31/06/2005 it assumes it's a correct date.

Any suggestions?

Thanks

Luis Verme

 

[Bunter_22]

Hi Luis,

If I am reading your code correctly any date you pass it will be
correct, both sides of your if statement are the same?

Regards,

James

 

[Luis Verme]

Oops.
It's

If IsDate(txtFFinal) Then
MsgBox "Date is correct"
else
MsgBox "Date is incorrect"
End If

 

[Luis Verme]

But of course it still doesn't work

Oops.
It's

If IsDate(txtFFinal) Then
MsgBox "Date is correct"
else
MsgBox "Date is incorrect"
End If

 

[Bunter_22]

What are you inputting to? a userform, spreadsheet etc.

I tried putting your code behind a userform textbox and it worked fine.
If its on the spreadsheet then I think that depends on your regional
settings as to whether its a correct date, ie in the Uk 31/06/2005 is a
valid date.

Regards,

James

 

[Norman Jones]

Hi Bunter,

ie in the Uk 31/06/2005 is a

valid date.

Certainly NOT in the UK and not in any country that I've encountered!

June has only 30 days.

 

[Bunter_22]

Oops, I never get that rhyme correct! You would think as its only 6th
July as well that I would have realised June had only 30 days.

To try and redeem myself how about this workaround?

Dim DateTemp As String
DateTemp = txtffinal
DateTemp = Mid(DateTemp, 4, 2) & "/" & Left(DateTemp, 2) & "/" &
Right(DateTemp, 2)
If IsDate(DateTemp) Then
MsgBox "Date is correct"
Else
MsgBox "Date is incorrect"
End If

You could use txtffinal directly but I wasn't sure if you were using it
somewhere else etc.

James

 

[Norman Jones]

Hi Luis,

I tried:

Sub Tester01()
MsgBox IsDate("31/6/2005")
MsgBox IsDate("30/6/2005")
MsgBox IsDate("31/02/2005")
End Sub

and received the responses: False True False - as expected

However, trying:

Sub Tester02()
MsgBox IsDate("31/6/05")
MsgBox IsDate("30/6/05")
MsgBox IsDate("31/02/05")
End Sub

I received a True response in each case, which surprised me.

In any event, using the full 4-digits for the year I could not reproduce
your experience.

 

[Norman Jones]

Hi James,

Your code returns invalid for invalid dates.

Unfortunately, testing I was unable to get it to acknowledege *any* date as
valid - although I only tried a representative few.

 

[Bunter_22]

Hi Norman,

I did test quite a few dates before I posted, I have subsequently found
that if you enter in the format of dd/mm/yy then it works everytime but
in the format of mm/dd/yy then it doesn't. The way I tested way to say
Txtffinal = Inputbox(" ") then just gave it varying values.

Does this clarify anything? How did you test?

Regards,

James

 

[Norman Jones]

Hi James,

Does this clarify anything? How did you test?

Sub TestJames(sStr)
Dim DateTemp As String
DateTemp = sStr
DateTemp = Mid(DateTemp, 4, 2) & "/" & _
Left(DateTemp, 2) & "/" & Right(DateTemp, 2)
If IsDate(DateTemp) Then
MsgBox "Date is correct" & vbNewLine & CDate(sStr)
Else
MsgBox "Date is incorrect"
End If

End Sub

Sub TryIt()
Dim arr As Variant
Dim i As Long

arr = Array("1/6/05", "2/6/05", "3/6/05", _
"20/6/05", "24/6/05", "30/6/05", _
"31/6/05", "29/2/05")

For i = LBound(arr) To UBound(arr)
TestJames arr(i)
Next
End Sub

 

[Bunter_22]

Hi Norman,

When I input dates I always but 01/01/05 rather than 1/1/05 perhaps I
am wrong/unique, if you change your array to:
arr = Array("01/06/05", "02/06/05", "03/06/05", _
"20/06/05", "24/06/05", "30/06/05", _
"31/06/05", "29/02/05")
It then works. Which includes the original example of 31/06/2005.

Regards,

James

 

[Norman Jones]

Hi James,

When I input dates I always but 01/01/05 rather than 1/1/05

Being lazy, I might do either but the latter style is certainly used.

Perhaps it might be better to allow for different entry styles. Perhaps by
using Instr / InstrRev to return the date separators and then grabbing the
values between separators.

Amending all the test dates to your stipulated dd/mm/yy format and replacing
your msgboxes with debug.print statements, I get:

Date is correct 01/06/2005
Date is correct 02/06/2005
Date is correct 03/06/2005
Date is correct 20/06/2005
Date is correct 24/06/2005
Date is correct 30/06/2005
Date is incorrect 05/06/1931
Date is incorrect 05/02/1931

The left column is fine, but not necessarily the righthand column.

 

[Bunter_22]

Hi Norman,

Yeah I was thinking about adding the instr function however I just
thought it was getting complicated for what should be simple - I should
remember that the simple things are often the hardest to do.

This should now work for what Luis wants to do, if what Luis is passing
it is in the format of dd/mm/yy then it shouldn't be necessary to add
the instr function.

Regards,

James

 

[Luis Verme]

Hi guys.
Thank you for all your suggestions. As Norman said, I changed to a 4 digit
year date and worked fine.

Thanks again

Luis Verme

 

[William Benson]

Norman,

What is surprising?

Just put this in Immediate Window and the answer is clear



?format(("31/6/05"),"M/D/YYYY")

 

[Norman Jones]

Hi Bill,

What is surprising?

Just put this in Immediate Window and the answer is clear



?format(("31/6/05"),"M/D/YYYY")

See my (previously) final post in the thread which includes:

Amending all the test dates to your stipulated dd/mm/yy format and
replacing your msgboxes with debug.print statements, I get:

Date is correct 01/06/2005
Date is correct 02/06/2005
Date is correct 03/06/2005
Date is correct 20/06/2005
Date is correct 24/06/2005
Date is correct 30/06/2005
Date is incorrect 05/06/1931
Date is incorrect 05/02/1931

The left column is fine, but not necessarily the righthand column.

So my surprise was short lived but thank you for underlining the
explanation.

 

[William Benson]

Gotcha ... there were some posts in that thread that I skipped, pls forgive
me!

 

[Guest]

Hi,
Have I missed the point here; or do you really need to validate?
Why not just use "date()" command, and it will adjust "31/6/05" to 1/7/05"
for you anyway?
Best, Randall

 

[Norman Jones]

Hi Randall,

Have I missed the point here; or do you really need to validate?
Why not just use "date()" command,

I assume you mean Excel's worksheet function Date().

If so, the required syntax for the function is: DATE(year,month,day).

Two points spring from this:

(1) The function cannot be used (directly) on dates in the format indicated
by the OP's opening line:

This objection is, however, clearly superable


(2) You give the example:

Why not just use "date()" command, and it will adjust "31/6/05" to 1/7/05"

But what makes you think that "31/6/05" and "1/7/05" are necessarily
conterminous?

And *if* you believe that they are, do you also believe that the following
results, from the intermediate window, are what the OP might intuitively
expect as validated dates:

?format([date(2005,13.5,34)], "mmm/dd/yyyy")
Mar/06/2005

?format([date(2005,pi(),pi())], "mmm/dd/yyyy")
Mar/03/2005

?format([date(2005,11.5,77)], "mmm/dd/yyyy")
Jan/16/2006