How to validate if a textbox is in the correct date format?

Question

I have a UserForm where in a TextBox if user inputs a date format other than dd/mm/yyyy then an error message will pop up after clicking the submit button. Here is what I have so far, it only formats an invalid date input to dd/mm/yyyy but does not display the validation:

If IsDate(Me.DOBTextBox.Value) Then
    Me.DOBTextBox = Format(Me.DOBTextBox.Value, "dd/mm/yyyy")

ElseIf Not IsDate(Me.DOBTextBox.Value) Then
    MsgBox "Please enter a valid date format dd/mm/yyyy", vbCritical
    DOBTextBox.SetFocus
    Exit Sub
End If

Here is an interesting [link](https://stackoverflow.com/questions/12012206/formatting-mm-dd-yyyy-dates-in-textbox-in-vba) — JvdV, Jan 24 '19 at 08:29
@JvdV I've tried out this custom calendar but I found out its a bit complicated for me. — Useruser, Jan 24 '19 at 08:58

Pᴇʜ · Accepted Answer · 2019-01-24T09:18:36.983

2

Validate if there are 2 slashes / in it and day <= 31 and month <= 12:

Dim ArrInput As Variant
ArrInput = Split(Me.DOBTextBox.Value, "/")

Dim ValidDate As Boolean

If UBound(ArrInput) = 2 Then 'make sure there are exactly two slashes in the date
    If ArrInput(1) > 0 And ArrInput(1) <= 12 And _
    ArrInput(0) > 0 And ArrInput(0) <= 31 Then     'month <=12 & day <= 31
        ValidDate = True
    End If
Else
    ValidDate = False
End If

If Not ValidDate Then
    MsgBox "Please enter a valid date format dd/mm/yyyy", vbCritical
    DOBTextBox.SetFocus
    Exit Sub
End If

'code here that executes when date is valid
Dim MyValidDate As Date
MyValidDate = DateSerial(ArrInput(2), ArrInput(1), ArrInput(0))

Alternatively just try to convert the string date into a real date and check if day, month and year match the values in the string.

Dim ArrInput As Variant
ArrInput = Split(Me.DOBTextBox.Value, "/")

Dim ValidDate As Boolean

If UBound(ArrInput) = 2 Then 'make sure there are exactly two slashes in the date
    Dim MyValidDate As Date
    MyValidDate = DateSerial(ArrInput(2), ArrInput(1), ArrInput(0))

    If Day(MyValidDate) = CLng(ArrInput(0)) And _
       Month(MyValidDate) = CLng(ArrInput(1)) And _
       Year(MyValidDate) = CLng(ArrInput(2)) Then
        ValidDate = True
    End If
End If

If Not ValidDate Then
    MsgBox "Please enter a valid date format dd/mm/yyyy", vbCritical
    'DOBTextBox.SetFocus
    Exit Sub
End If

'code here that executes when date is valid
MsgBox "date is valid " & MyValidDate

edited Jan 24 '19 at 09:18

answered Jan 24 '19 at 08:26

Pᴇʜ

56,719
10
49
73

Hello @Peh, just wondering. What would happen here if the user is used to type mm/dd/yyyy and enters a date 02/03/2019. He obviously wants to say third of feb, but OP here will pick up 2nd of march. Is my assumption correct? – JvdV Jan 24 '19 at 08:31
1

Well you cannot prevent that other than using a date format that cannot be misunderstood like the format `YYYY-MM-DD` according to [ISO 8601](https://en.wikipedia.org/wiki/ISO_8601) (which I highly recommend to use). • I mean if you tell the user to put in `dd/mm/yyyy` and he puts it wrong you cannot prevent it. That's why `YYYY-MM-DD` is so mighty. It the only date format that cannot be misunderstood if entered as string. • You just validate the format. You cannot validate if the user put in the correct date (just if it is a valid date according to the format). – Pᴇʜ Jan 24 '19 at 08:33
You are correct, thanks for the quick respons. Making what OP is trying to do here prone for faulty input imo. I know this isn't the place to got indept discussion over this, but using a datepicker cancels out errors, or if you must use a textbox, why not use three (day, month and year). – JvdV Jan 24 '19 at 08:38
It is the same like if the user actually wants to put in `02/03/2019` but actually accidentally put's in `03/03/2019` (a datepicker or something else would not prevent that). Disadvantages of date pickers or 3 TextBoxes are you cannot copy/paste a date. Which as a knock out (at least for me). • I tend to force users to think more. If they put the wrong date, they put the wrong date. They will notice or not. We cannot force them to get it right. – Pᴇʜ Jan 24 '19 at 08:40
Hi @Pᴇʜ thanks for this resolution! However I noticed that this code doesnt put into consideration the different amount of days there are in certain months. (Jan is 31 days, Feb 29 days, April 30 days etc.) When i click the submit button it accepts an invalid date (30/02/2019) I've been trying to work it out, do you have any ideas? – Useruser Jan 24 '19 at 09:01
See my alternative, which should be more accurate. – Pᴇʜ Jan 24 '19 at 09:19

How to validate if a textbox is in the correct date format?

1 Answers1