InputBox to ask for date and only accept standard date formats

[AndyTampa]

I have this spreadsheet I use with templates on it. I am constantly typing the same notes into this one template so I started creating buttons that will append the text with more text. I've ventured into message boxes and input boxes for when I didn't fill a cell or just need something specific added in the middle of the text. I've never used these boxes before.

I've got one button that I've (sort of) gotten to work, but I'd like to perfect it if possible. It's an input box that asks for a date. I've tried all sorts of solutions I've found online but there are issues with all of them.

Here's what I've got so far.

Private Sub CommandButton19_Click()

Dim String1 As String
Dim TDate As String

String1 = Range("PCase")

TDate = InputBox("Please enter the new FTD in the format MM/DD/YYYY.", "New FTD")
   
If IsDate(TDate) Then
    TDate = CDate(TDate)
    Range("ActTaken") = Range("ActTaken") & " The existing FTD has been removed from case" & String1 & _
    " and replaced with a " & TDate & " FTD as "
Else
    MsgBox "Oops. Next time enter a date."
End If

End Sub

This mostly works. However, IsDate accepts decimal numbers and provides output as a time and also accepts short month-day inputs, converting them to a date and assuming they are 2024. The only formats I want to be accepted are mm/dd/yyyy, mm/dd/yy or m/d/yy.

I've tried testing the input with the below but get a Type Mismatch error if it's not right.

If Not VBA.Format(TDate, "mm/dd/yyyy") Then

Format doesn't work without the VBA. in front of it.

I've also tried testing the input with the below but it won't accept dates like 5/5/24.

If TDate Like "[0-1][0-9]/[0-3][0-9]/[1-2][0-9][0-9][0-9]" Then

Since I'm unfamiliar with the InputBox, I was wondering if there was any way to force it to only accept actual dates only.

 

[Jaafar Tribak]

Haven't really gone through the entire thread but the title reminded me of this textbox with date input mask in case the solutions that have been suggested don't work for you.

 

[AndyTampa]

I'm still working on figuring out how this works. The volume of work has greatly increased, so I have extremely little time to study this. I see possibilities for the use of a Function that I had never considered. I also found other things I wanted to change. I have 23 buttons so far and there are obvious pieces of code that are used in multiple buttons that can be converted into a separate sub and called as needed. This function is one of those items. I have about 4 buttons that require a date and it appears that this will work perfectly once I can wrap my head around it to implement it.

 

[AndyTampa]

Haven't really gone through the entire thread but the title reminded me of this textbox with date input mask in case the solutions that have been suggested don't work for you.

It looks overly complicated for my purposes. I am nowhere near the level needed to understand all that code, but thank you anyway.

 

[Akuini]

@AndyTampa
After I answered your question by writing "Function True_Date," I decided to write a more generic function, called Function Correct_Date, to obtain the correct date when converting text to a date. Here's the code:

Function Correct_Date(ByVal date_format As String, ByVal txt_Date As String, ByRef Output_date As Date) As Boolean
'In this function, what is considered a valid date has the following criteria:
'The year must be 2 or 4 digits or blank. If it's 2 digits, then it will be preceded by "20". If it's blank then then it will be this year.
'The day, month, and year are not changed in the result obtained from the text-to-date conversion using DateSerial Function.

'The arguments:
'1. date_format: "dmy" or "mdy" or "ymd"
'2. txt_Date: the input string, such as "12-4-2024", the separator can be one of these characters: -/.\
'3. Output_date: a date variable that will store the date obtained from the text-to-date conversion

Dim TD As Date

Output_date = Empty

txt_Date = Replace(txt_Date, "-", "/")
txt_Date = Replace(txt_Date, ".", "/")
txt_Date = Replace(txt_Date, "\", "/")

If IsDate(txt_Date) Then
    dt = Split(txt_Date, "/")
    
    If UBound(dt) = 1 Then
        If LCase(date_format) = "dmy" Or LCase(date_format) = "mdy" Then
            txt_Date = txt_Date & "/" & Year(Date)
        Else
            txt_Date = Year(Date) & "/" & txt_Date
        End If
        dt = Split(txt_Date, "/")
    End If
   
    Select Case LCase(date_format)
        Case "dmy":  a = dt(2): b = dt(1): c = dt(0)
        Case "mdy":  a = dt(2): b = dt(0): c = dt(1)
        Case "ymd":  a = dt(0): b = dt(1): c = dt(2)
        Case Else
            MsgBox "The first argument of Correct_Date function must be 'dmy' or 'mdy' or 'ymd'."
            Exit Function
    End Select
    
    
    If IsDate(txt_Date) And (a Like "####" Or a Like "##") Then
         
         If a Like "##" Then a = "20" & a
         
         On Error Resume Next
          TD = DateSerial(a, b, c)
             If Err.Number = 0 Then
               If Year(TD) = Val(a) And Month(TD) = Val(b) And Day(TD) = Val(c) Then
                    Correct_Date = True
                    Output_date = TD
               End If
             End If
         On Error GoTo 0
         
    End If
End If
End Function

This function can be useful in the data entry process, where users type text in a textbox or input box, and the text is converted to a date. It helps prevent users from inputting the wrong date. For example, in dmy format, if a user types "2-13-2024," it will be rejected because there is no month 13.

The function accomplishes two tasks:
1. It checks whether the input text is a valid date input.
2. If it's a valid date then it generates the date as the result of text-to-date conversion base on date format you have chosen.

Here's an example:

Sub test_1()
Dim myDate As Date
Dim tx As String

tx = "2-1-24"
If Correct_Date("dmy", tx, myDate) Then
    Debug.Print myDate
    Debug.Print Month(myDate)
Else
    Debug.Print "Wrong"
End If

If Correct_Date("mdy", tx, myDate) Then
    Debug.Print myDate
    Debug.Print Month(myDate)
Else
    Debug.Print "Wrong"
End If
End Sub

This function is one of those items. I have about 4 buttons that require a date and it appears that this will work perfectly once I can wrap my head around it to implement it.

If you can explain in more detail what the 4 buttons should do, I can probably simplify the code to suit.

 

[Tupe77]

@Akuini the codes are always of good quality, but can it also solve your problem?

He is able to check that the date is real and is punctuated by "/", but how to define the real date?

The only formats I want to be accepted are mm/dd/yyyy, mm/dd/yy or m/d/yy.

If I understand correctly, you want to accept the input 12/12/12, but then the end result is a trade of chance, because:
The user could to mean with his input (dd/mm/yy), (mm/dd/yy), (yy/mm/dd/), (yy/mm/dd).

If we calculate that there are 30 days in a month, then 12/30*100=40% of the dates can be entered incorrectly no matter how they are checked. So there are 12 days in a month when we cannot know whether it is a day or a month.
I mean the rules are:
a month cannot be greater than 12
The day cannot be bigger than 31
A year can be practically anything.

You probably need to come up with more rules or rethink your approach?

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

 

[Akuini]

@Tupe77
Thanks for reviewing my code.

If I understand correctly, you want to accept the input 12/12/12, but then the end result is a trade of chance, because:
The user could to mean with his input (dd/mm/yy), (mm/dd/yy), (yy/mm/dd/), (yy/mm/dd).

If you're referring to my last code in post #14, namely Function Correct_Date, then that's what the code is intended to solve — obtaining the correct date format as desired.
When setting up a data entry process for entering a date, you can't allow users to freely choose the date format. You should determine what format to be used beforehand and ensure that users understand that.
In the case of using Function Correct_Date, you need to choose one of three formats: dmy, mdy, or ymd, and then set it as the first argument of the function (ByVal date_format As String).
For example, let's say you have a textbox and you want users to enter dates with the dmy format, and then press a command button to send the date to cell A1. You can do it like this:

Private Sub CommandButton1_Click()
Dim myDate As Date
Dim tx As String

tx = TextBox1
If Correct_Date("dmy", tx, myDate) Then  'using dmy format
    Range("A1") = myDate
Else
    MsgBox "Wrong input"
End If

End Sub

Note:
Actually, Function Correct_Date isn't intended to address the specific issue raised by the OP, but rather to provide a generic function for obtaining the correct date when converting text to a date. As I mentioned:

After I answered your question by writing "Function True_Date," I decided to write a more generic function, called Function Correct_Date, to obtain the correct date when converting text to a date.

You probably need to come up with more rules or rethink your approach?

In my testing thus far, I haven't encountered any issues when using Function Correct_Date. Could you provide an example where it produced an incorrect result?

 

[Tupe77]

@Akuini First of all, I'm really sorry that you understood my writing as a criticism of you or your code. It wasn't!

2. I didn't review your code, because from previous experience with your codes I know it works as intended. (Like Search deList)
but: Checking a user-supplied date from an inputbox has been a data processing problem for more than 60 years. Although I know your skills in vba code are excellent, I think it unlikely that you will finally solve that problem that was considered impossible.

3. (Akuini) You should determine what format to be used beforehand and ensure that users understand that.

(AndyTampa) (The only formats I want to be accepted are mm/dd/yyyy, mm/dd/yy or m/d/yy. )

I mean the date rules are:
Month cannot be greater than 12
Day cannot be bigger than 31
A year can be practically anything.
With normal date rules, you just can't programmatically check that the input is in mm/dd/yy format, because it can also be yy/mm/dd or dd/mm/yy. (For at least 12 days every month.)
This is why we all use a very slow and clumsy Date Picker on forms, programmers don't trust users, for a good reason.
I don't know if that's a problem for AndyTampa, but I thought it might be worth his attention.

In short. Why must the entered date even be checked if we trust the user to enter it in the agreed format? It seems that AndyTampa does not trust it strongly enough.

If the misunderstanding is due to my English, I am of course very sorry for that, but at least I have tried to warn about it:
My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

And Sorry again!

 

[Akuini]

In short. Why must the entered date even be checked if we trust the user to enter it in the agreed format?

Let's consider the textbox example above, which utilizes the dmy format.
Let's say a user types '2-13-24'. There are two possibilities:
1. They might believe it's in the mdy format, which is incorrect.
2. It could simply be a mistype. Even when a user understands the intended format, errors can still occur.
The Correct_Date function will reject this input, prompting the user to recognize the mistake. The notification could be more specific, stating: 'Wrong input. Please enter the date in dmy format, such as '15-1-2024'.'
Of course there's a problem, if the user thinks it's mdy format and enter something like "1-2-24" and thinking it's January 2nd while the code will read it as February 1st. In such cases, the code won't identify the problem.

To mitigate this, one solution is to display another message box showing the date entered with the month name, for example:

Private Sub CommandButton1_Click()
Dim myDate As Date
Dim tx As String

tx = TextBox1
If Correct_Date("dmy", tx, myDate) Then
    If MsgBox("You're going to enter this date: " & Format(myDate, "dd-mmmm-yyyy"), vbOKCancel, "") = vbOK Then
        Range("A1") = myDate
    End If
Else
    MsgBox "Wrong input"
End If

End Sub

With normal date rules, you just can't programmatically check that the input is in mm/dd/yy format, because it can also be yy/mm/dd or dd/mm/yy. (For at least 12 days every month.)
This is why we all use a very slow and clumsy Date Picker on forms, programmers don't trust users, for a good reason.
I don't know if that's a problem for AndyTampa, but I thought it might be worth his attention.

I agree with you; a date picker is more suitable for this kind of task. However, for various reasons, not all people like to or can set it up. Therefore, the Correct_Date function serves as an alternative method, although it's less reliable.

And no need to apologize; your input is very valuable for improving the solutions offered in this matter.

 

[Akuini]

@Akuini First of all, I'm really sorry that you understood my writing as a criticism of you or your code. It wasn't!

Actually, I need to apologize. I mistakenly thought that in post #15 you were talking to me because you mentioned @Akuini at the beginning, when you were actually talking to the OP. So, I'm sorry for the misunderstanding.

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."

English is not my native language either. I can write in English, but usually with many errors. Therefore, I sometimes use ChatGPT to correct my English.

 

[Tupe77]

Actually, I need to apologize. I mistakenly thought that in post #15 you were talking to me because you mentioned @Akuini at the beginning, when you were actually talking to the OP. So, I'm sorry for the misunderstanding.

English is not my native language either. I can write in English, but usually with many errors. Therefore, I sometimes use ChatGPT to correct my English.

I didn't notice when I wrote how using your name with @ made the sentence misleading.
Problems like this are caused/increased by my English. I write and read English, but not enough to realize how ambiguous some of my sentences are...
And yes, I meant the content (point) of the message to the OP (Andy Tampa) even though I was referring to you.

I agree with you; a date picker is more suitable for this kind of task. However, for various reasons, not all people like to or can set it up.

I didn't exactly mean that, because I hate it when I'm a user... But on the other hand, it is a bulletproof solution.

One solution is to display another message box showing the date entered with the month name.

That is a good solution, but still leaves a small chance for mistakes, because according to my experience, about 20% of users press ok without reading the message.
On the other hand, the fault then lies solely with the user himself.
This is even better in the form where we can show the final result in real time without a pop-up window.

The third option is to require the user to enter the date in mmm/dd/yyyy format, in which case we can check that the year has four numbers, the day has 2 numbers and the month has 3 letters. In that case, the order doesn't even matter.

The problem with all three solutions is that they reduce usability. That's why it's always a choice between usability and input reliability.
That's why every OP should open up the code's operating environment a little more. All my comments are absolutely pointless if it is a school programming task, but they may be of considerable importance in the company's invoicing or salary payment.

My apologies for any quirks, English is not my native language. "So I blame Google translate for all my goofs."