Converting date as string to a number using VBA

[seleseped]

Many thanks in advance for your time and help . . .

I have a simple user form where the date of an office visit is entered (as mm/dd/yy in OVDateTextBox) and the difference between that date and today's date is calculated. I am trying to build in protection against user entry errors where a message box appears if the user enters a date:
- in the future (which prompts MsgBox "Date of last office visit cannot be in the future.")
- not in the format mm/dd/yy (which prompts MsgBox "Please enter a valid date in the format mm/dd/yy.")

My problem is that VBA does not see the two dates as numbers so it does not properly identify a future date nor does it recognize incomplete or improper format entry (for example is a date is entered simply as mm/dd). I'm sure I've not correctly declared my date variables but cannot figure out a solution. Here's my attempted code:

Dim DateOne As Date, DateTwo As Date, Months As Integer, RefillMonths As Integer, OVDate As Integer
Private Sub CalculateCommandButton_Click()
On Error GoTo ErrorHandler

DateOne = CDate(OVDateTextBox.Value) 'office visit date
DateTwo = CDate(Date) 'today's date
Months = DateDiff("m", DateTwo, DateOne) 'time elapsed between office visit date and today

If OVDateTextBox.Value = "" Then
MsgBox "Please enter date of last office visit."
OVDateTextBox.Value = ""
If DateOne > DateTwo Then MsgBox "Date of last office visit cannot be in the future."

ErrorHandler:
MsgBox "Please enter a valid date in the format mm/dd/yy."
Exit Sub

'The following part functions appropriately and is not part of my question.
Else: RefillMonths = 18 - Months
If RefillMonths < 2 Then 'time between today (DateTwo) and OV (DateOne)
RefillMonths = 2
ElseIf RefillMonths > 12 Then
RefillMonths = 12
End If
MsgBox "Refill for " & RefillMonths & " months."
End If
OVDateTextBox.Value = ""
Unload Me

End Sub

 

[Norie]

There's nothing wrong with your declarations as far as I can see.

One thing that is wrong using CDate with Date, the Date function returns a date so doesn't need to be converted.

As far as checking for a valid date being entered take a look at the IsDate function.

If Not IsDate(OVDateTextBox.Value) Or OVDateTextBox.Value="" Then
    MsgBox "Please enter a valid date for OVDate"
    Exit Sub
End If

DateOne = CDate(OVDateTextBox.Value) 'office visit date
DateTwo = Date 'today's date
Months = DateDiff("m", DateTwo, DateOne) 'time elapsed between office visit date and today

' rest of code...

 

[seleseped]

Thanks for the suggestion but I didn't have any luck.

If I enter 1/1 as the date in the text box and debug the code, when I hover over the OVDateTextBox.Value variable, the 'hover message' says OVDateTextBox.Value = "1/1" which I think is telling me that VB sees the date as a text string. Accordingly, the code then runs past the MsgBox "Please enter a valid date for OVDate" and proceeds to the next section. If I continue to run the code, the 'hover message' over the DateOne variable says OVDateTextBox.Value = 1/1/16 (no quotes and the year '16 is automatically appended) and the 'hover message' over DateTwo says Date = 2/16/2016.

Here's the code as it now reads (with your suggestion incorporated):

Private Sub CalculateCommandButton_Click()
On Error GoTo ErrorHandler


If Not IsDate(OVDateTextBox.Value) Or OVDateTextBox.Value = "" Then
MsgBox "Please enter a valid date for OVDate"
Exit Sub
End If

DateOne = CDate(OVDateTextBox.Value) 'office visit date
DateTwo = Date 'today's date
Months = DateDiff("m", DateTwo, DateOne) 'time elapsed between office visit date and today

If OVDateTextBox.Value = "" Then
MsgBox "Please enter date of last office visit."
OVDateTextBox.Value = ""
If DateOne > DateTwo Then MsgBox "Date of last office visit cannot be in the future."

ErrorHandler:
MsgBox "Please enter a valid date in the format mm/dd/yy."
Exit Sub

 

[Norie]

If you enter something like 1/1 then Excel will think you are trying to enter a date with the year missing, so it will helpfully add the year for you.

What date do you want 1/1 to represent?

PS Are you sure the second date isn't 2/19/2016 ie today's date in mm/dd/yyyy format?

 

[seleseped]

OVTextBoxDate.Value can be a date from any year (but probably nothing before 2013). What I'm hoping to accomplish with the message warning is to avoid entering simply a month and day without a year and then having the rest of the calculations be off by 12, 24, 36, etc. months because Excel made the assumption that the year should be 2016. And you are correct - I made a typo and the second date is 2/19/2016.

Again, thanks for your reply.

 

[Norie]

If you want to check if the user has entered a year you'll either need add further checks on what the've entered or provide another method for the user to select/enter a date.

 

[seleseped]

OK, thanks. What I thought was an easily-solved problem is turning out to be not-so-easily-solved. I am going to investigate adding a drop-down calendar or other options. Do you have any suggestions?