IsDate on TextBox

  • Hi

    I am formating a TextBox with the following code

    However if I put a date of 31/06/16, instead of displaying the MsgBox it Give a date of 16-Jun-31

    Can anyone help please

  • 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

    I have amended the code to read:

    With TextBox2
        If IsDate(TextBox2.Value) Then
            TextBox2 = Format(TextBox2, "dd-mmm-yyyy")
            TextBox2.Value = ""
            MsgBox "Please Enter a Vaid Date", vbCritical, "Date Error"
            Exit Sub
        End If
    End With

    However the date still returns 16-June-1931

    What am I missing please?

  • 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).


    31 June 16

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

    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.

    [Written freehand - untested]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!