IsDate on TextBox

Re: IsDate on TextBox

Because there is no month 16 or 31, the '06' is assumed to be the Month (June). June has 30 days so the 16 is assumed to be the day leaving 31 as the Year.

Because of the Century break (Can't remember exactly what it is now, but 2 digit years less than that number are assumed to be in the 21st C, while 2 digits years greater than that are assumed to be 20th C) , the 31 is assumed to be 1931...

This is particular to your example date of 31/06/16 and other invalid 'dates'. An input date of 30/06/16 would be evaluated correctly according to your Regional settings.

The only way to really get around the assumptions is to enter the date using a 4 digit year.

Re: IsDate on TextBox

As an aside, the line

Cancel = True

will error unless you have a module level variable named 'Cancel' (And you shouldn't - 'Cancel' is a VBA keyword. It is not a good idea to name variables the same as in-built keywords)

Re: IsDate on TextBox

You can play around with the code all you want, but unless the user input is absolutely unambiguous then VBA will make assumptions when the various components of the date are invalid (Like June 31st).

Wrong:

31/06/16
31 June 16

Right: (But invalid dates will be caught by the code

31/06/2016
31 June 2016
30 06 16
30 June 2016

If the date is valid then 30/06/16 will evaluate correctly. All your problems start when an invalid date is entered.

But if you enter a 4 digit year, you can do something like 2016 june 30. This is totally unambiguous - 4 digit year, month spelled in full (or with the accepted abbreviation) and that just leaves the '30' as the day.

If you just enter something like 30/06 then the year will be assumed to be the year of the current date, but if it has the wrong number of days for the month (31/06) then assumptions will be made as before. Month is 6, Year is 31 (as in 1931) and the day will be 01.

Of course you can always check the derived date and if the year is in the 1900's then you know it's wrong.

Only you can decide which is the best approach for your particular situation but dates can be a minefield...

Re: IsDate on TextBox

First check if the contents of the textbox can be coerced to a date and then use the CDate() function to convert to a true date. Then simply check the year of that date.

Private Sub tb_AfterUpdate()


   On Error GoTo Catch


   With tb


      If IsDate(.Text) Then


         If Year(CDate(.Text)) >= 2015 Then
            .Text = Format(CDate(.Text), "dd-Mmm-yyyy")
            Exit Sub
         End If
      End If


Catch:
      .Text = ""
      MsgBox "Please Enter a Valid Date", vbExclamataion, "Input Error"


      '// Earlier comment about 'Cancel' still applies
      '// Cancel = True


      .SetFocus
   End With


End Sub

[Written freehand - untested]