Validating Dates
 

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

Validating Dates
 

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

Validating Dates
 

Oops.
It's

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

escribió en el mensaje
oups.com...
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

Validating Dates
 

But of course it still doesn't work

"Luis Verme" escribió en el mensaje
...
Oops.
It's

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

escribió en el mensaje
oups.com...
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

Validating Dates
 

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

Validating Dates
 

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.

---
Regards,
Norman



wrote in message
ups.com...
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

Validating Dates
 

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

Validating Dates
 

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.


---
Regards,
Norman



"Luis Verme" wrote in message
...
But of course it still doesn't work

"Luis Verme" escribió en el mensaje
...
Oops.
It's

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

escribió en el mensaje
oups.com...
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

Validating Dates
 

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.

---
Regards,
Norman



wrote in message
oups.com...
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

Validating Dates
 

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

Validating Dates
 

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

---
Regards,
Norman



wrote in message
ps.com...
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

Validating Dates
 

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

Validating Dates
 

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.

---
Regards,
Norman



wrote in message
oups.com...
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

Validating Dates
 

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

Validating Dates
 

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
"Norman Jones" escribió en el mensaje
...
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.


---
Regards,
Norman



"Luis Verme" wrote in message
...
But of course it still doesn't work

"Luis Verme" escribió en el mensaje
...
Oops.
It's

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

escribió en el mensaje
oups.com...
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

Validating Dates
 

Norman,

What is surprising?

Just put this in Immediate Window and the answer is clear

:-)

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



"Norman Jones" wrote in message
...
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.


---
Regards,
Norman



"Luis Verme" wrote in message
...
But of course it still doesn't work

"Luis Verme" escribió en el mensaje
...
Oops.
It's

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

escribió en el mensaje
oups.com...
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

Validating Dates
 

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.


---
Regards,
Norman

Validating Dates
 

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

"Norman Jones" wrote in message
...

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.


---
Regards,
Norman

Validating Dates
 

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

Validating Dates
 

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:

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

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

---
Regards,
Norman


"randallc" wrote in message
...
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

Validating Dates
 

????

I get

?format([date(2005,13.5,34)], "mmm/dd/yyyy")
Feb/02/2002

or

?format([date(2005,13.5,34)], "mmm/dd/yyyy")
Feb/03/2006

depending on which Date system I'm using, but nothing like what you
got...


In article ,
"Norman Jones" wrote:

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

Validating Dates
 

Hi JE,

I have retried all thee dates in the intermediate window and, as you
correctly point out, the first of the three returns: Feb/03/2006 (on my
system).

I ran my date checks and then played with them whilst writing the post,
with the result you highlight.

Thank you for the correction.

---
Regards,
Norman



"JE McGimpsey" wrote in message
...
????

I get

?format([date(2005,13.5,34)], "mmm/dd/yyyy")
Feb/02/2002

or

?format([date(2005,13.5,34)], "mmm/dd/yyyy")
Feb/03/2006

depending on which Date system I'm using, but nothing like what you
got...


In article ,
"Norman Jones" wrote:

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

Validating Dates
 

The biggest issue for me is that the example you gave evaluates an XL
function that depends on the user's Date System setting, without
checking that setting. That can lead to significant errors.

I see it all the time when people write code that assumes that the user
will be using the 1900 date system.

In article ,
"Norman Jones" wrote:

I have retried all thee dates in the intermediate window and, as you
correctly point out, the first of the three returns: Feb/03/2006 (on my
system).

I ran my date checks and then played with them whilst writing the post,
with the result you highlight.

Thank you for the correction.

---
Regards,
Norman



"JE McGimpsey" wrote in message
...
????

I get

?format([date(2005,13.5,34)], "mmm/dd/yyyy")
Feb/02/2002

or

?format([date(2005,13.5,34)], "mmm/dd/yyyy")
Feb/03/2006

depending on which Date system I'm using, but nothing like what you
got...


In article ,
"Norman Jones" wrote:

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