• If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk. If you have any questions regarding an article, please use the Article Discussion section.
Akuini

Excel VBA: Function to Obtain Correct Date Format When Converting Text to Date

Excel Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
  6. 2011
  7. 2010
  8. 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:
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 Sub

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:
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 Sub

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:
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 Function
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:

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 Sub

2024-03-04_000823.jpg


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:

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 Sub

2024-03-05_200144.jpg


Another 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 Sub

Regards,

Akuini
Author
Akuini
Views
453
First release
Last update
Rating
0.00 star(s) 0 ratings

More Excel articles from Akuini

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top