- Excel Version
- 365
- 2021
- 2019
- 2016
- 2013
- 2011
- 2010
- 2007
Dealing with dates in VBA can be a bit tricky. When attempting to convert a string to a date, we may encounter unexpected results. The issues typically arise in the following ways:
- The day, month, and year might get swapped or changed unintentionally.
- What we typically consider as an incorrect date format may actually be considered valid by VBA.
My Windows date setting uses d-m-y format, so all examples below generate results base on that setting.
Example 1:
Using CDate, the day & month get swapped:
Example 2:
DateSerial function argument is always in this order: year,month,day, which is a good thing because we don't get confused.
However, one of the problem when using DateSerial is it accepts values that we would normally consider wrong such as day 32 or month 20.
Here's an example:
The best way to avoid this problem is probably by using a Date Picker. But if for whatever reason you decide not to use it then as an alternative, although it's a less reliable method, you can use a textbox (on userform or sheet) or inputbox.
Let's consider this example:
You have set up a textbox where users should input a date in the 'dmy' format. However, let's say a user types '2-13-24'. This is incorrect because there is no 13th month. There are two possibilities as to why this happens:"
1. The user 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.
To deal with such problems, I decided to write a function, called Correct_Date, to obtain the correct date when converting text to a date. It's not a bullet proof method, but it's more reliable than using only CDate or SerialDate function.
Here's the code:
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
The function returns two values:
1. A boolean value, checking whether the input text is a valid date input.
2. A real date value. If the input is valid, it generates the date as a result of text-to-date conversion based on the date format you have chosen.
How to use this function:
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 ( in a sheet) and you want users to enter dates with dmy format, and then press a command button to send the date to cell A1. You can do it like this:
Let's go back to the example when a user, in dmy setting, types '2-13-24'.
The Correct_Date function will reject this input, and you can set a message box prompting the user to recognize the mistake.
Of course there's a problem if the user thinks it's in mdy format and enter something like "1-2-24" and thinking it's 2nd January while the code will read it as 1st February. Unfortunately, in such cases, the function won't identify the problem.
However, to mitigate this, you can display another message box showing the date entered with the month name, and give the user the option to cancel. For example:
Another test:
Regards,
Akuini
- The day, month, and year might get swapped or changed unintentionally.
- What we typically consider as an incorrect date format may actually be considered valid by VBA.
My Windows date setting uses d-m-y format, so all examples below generate results base on that setting.
Example 1:
Using CDate, the day & month get swapped:
VBA Code:
Sub test_1()
'tested on dmy setting
Dim tx As String
tx = "13/1/2024"
Debug.Print Month(CDate(tx)) 'return 1, so it's January
tx = "1/13/2024"
'this input should be rejected because there is no month 13
'but CDate will interpret it as January 13th
Debug.Print Month(CDate(tx)) 'return 1
End SubExample 2:
DateSerial function argument is always in this order: year,month,day, which is a good thing because we don't get confused.
However, one of the problem when using DateSerial is it accepts values that we would normally consider wrong such as day 32 or month 20.
Here's an example:
VBA Code:
Sub test_2()
'tested on dmy setting
Debug.Print DateSerial(2024, 1, 32) 'return: 01/02/2024 , so 32-Jan become 1-Feb
Debug.Print DateSerial(2024, 20, 3) 'return: 03/08/2025
End SubThe best way to avoid this problem is probably by using a Date Picker. But if for whatever reason you decide not to use it then as an alternative, although it's a less reliable method, you can use a textbox (on userform or sheet) or inputbox.
Let's consider this example:
You have set up a textbox where users should input a date in the 'dmy' format. However, let's say a user types '2-13-24'. This is incorrect because there is no 13th month. There are two possibilities as to why this happens:"
1. The user 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.
To deal with such problems, I decided to write a function, called Correct_Date, to obtain the correct date when converting text to a date. It's not a bullet proof method, but it's more reliable than using only CDate or SerialDate function.
Here's the code:
VBA 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 = WorksheetFunction.Trim(txt_Date)
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 FunctionThe 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
The function returns two values:
1. A boolean value, checking whether the input text is a valid date input.
2. A real date value. If the input is valid, it generates the date as a result of text-to-date conversion based on the date format you have chosen.
How to use this function:
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 ( in a sheet) and you want users to enter dates with dmy format, and then press a command button to send the date to cell A1. You can do it like this:
VBA Code:
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 SubLet's go back to the example when a user, in dmy setting, types '2-13-24'.
The Correct_Date function will reject this input, and you can set a message box prompting the user to recognize the mistake.
Of course there's a problem if the user thinks it's in mdy format and enter something like "1-2-24" and thinking it's 2nd January while the code will read it as 1st February. Unfortunately, in such cases, the function won't identify the problem.
However, to mitigate this, you can display another message box showing the date entered with the month name, and give the user the option to cancel. For example:
VBA Code:
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 input this date: " & Format(myDate, "dd-mmmm-yyyy"), vbOKCancel, "") = vbOK Then
Range("A1") = myDate
End If
Else
MsgBox "Wrong input. Please enter the date in dmy format, such as '15-1-2024'"
End If
End SubAnother test:
VBA Code:
Sub test_3()
Dim myDate As Date
'these return TRUE
Debug.Print Correct_Date("dmy", "1-2-2024", myDate)
Debug.Print Correct_Date("dmy", "13-2-2024", myDate)
Debug.Print Correct_Date("dmy", "1-2-24", myDate)
Debug.Print Correct_Date("dmy", "1/2/2024", myDate)
Debug.Print Correct_Date("dmy", "1/2", myDate)
Debug.Print Correct_Date("dmy", "1.2.24", myDate)
'these return FALSE
Debug.Print Correct_Date("dmy", "1-13-2024", myDate)
Debug.Print Correct_Date("dmy", "31-4-2024", myDate)
End SubRegards,
Akuini
