How to Convert Date from String Using VBA (7 Ways)

If you are looking for some of the easiest ways to convert date from string using VBA, then you are in the right place. By using a VBA code you can easily convert a large chunk of strings to date easily. So, let’s get into the main article.

7 Ways to Convert VBA Date from String

Here, I have the following data table by which I will show the ways of converting date from string using VBA. I will also try to show the ways of this conversion in a random string.

For creating the article, we have used Microsoft Excel 365 version, you can use any other version according to your convenience.

Method-1: VBA Date from String for a Text String

Here, we will show the conversion for a random text string by using the CDATE function in a VBA Code.

Step-01:
➤ Go to Developer Tab >> Visual Basic Option

Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option

After that, a Module will be created.

Step-02:
➤ Write the following code

Sub datefromstring1()

Dim i As String
i = "1-10-22"
MsgBox CDate(i)

End Sub

Here, we have declared i as String and assigned i to a string “1-10-22”.

CDATE will convert this string into date.

➤Press F5

Result:
After that, you will get the following Message Box containing the date 1/10/2022 (mm/dd/yyyy).

Method-2: Converting a Value to Date Using CDATE Function

You can convert your desired value to date by following this method.

Step-01:
➤ Follow Step-01 of Method-1
➤ Type the following code

Sub datefromstring2()

Dim i As String
i = 44299
MsgBox CDate(i)

End Sub

Here, we have declared i as String and i is assigned to a value 44299.

CDATE will convert this value into a date.

➤ Press F5

Result:
Then, you will get the following Message Box containing the date 4/13/2021 (mm/dd/yyyy).

Read More: How to Convert Text to Date with Excel VBA

Method-3: VBA Date from String Using DATEVALUE Function

In this section, we will convert our desired string value into a date by using the DATEVALUE function in the VBA code.

Step-01:
➤ Follow Step-01 of Method-1
➤ Type the following code

Sub datefromstring3()

MsgBox DateValue("1/10/2022")

End Sub

Here, DATEVALUE will convert the text string “1/10/2022” into a date.

➤ Press F5

Result:
Afterward, you will get the following Message Box containing the date 1/10/2022 (mm/dd/yyyy).

Method-4: VBA Date from String Using FORMAT Function

Here, we will use the FORMAT function in the VBA code for converting the string into a date of our desired format.

Step-01:
➤ Follow Step-01 of Method-1
➤ Type the following code

Sub datefromstring4()

Dim i As String
i = 44299
MsgBox Format(CDate(i), "MM/DD/YYYY")

End Sub

Here, we have declared i as String and assigned i to a value 44299

CDATE will convert this string into date and after that FORMAT will convert the date into the desired format “MM/DD/YYYY”.

➤ Press F5

Result:
Finally, you will get the following Message Box containing the date 4/13/2021 (mm/dd/yyyy).

Method-5: Converting a Range of Strings to Date for Desired Format

Here, we have some strings that are in the text format (as they are left-aligned) in the Order Date column. By using a VBA code we will convert these strings into dates and get the output in the Real Date column.

Step-01:
➤ Follow Step-01 of Method-1
➤ Type the following code

Sub datefromstring5()

Dim i As Integer

For i = 5 To 11
    Cells(i, 4).value = Format(CDate(Cells(i, 3).value), "MM/DD/YYYY")
Next i

End Sub

Here, we have declared i as Integer and assigned i to a value 5 to 11 (within FOR loop)

The FOR loop will continue the operation of this conversion for rows 5 to 11 of Column C and will give the output values in Column D and CDATE will convert the strings into date and after that FORMAT will convert it into the desired format “MM/DD/YYYY”.

➤ Press F5

Result:
After that, you will get the dates converted from the text strings in the Real Date column.

Method-6: Converting a Range of Strings to Date Using DATEVALUE Function

You can convert the strings of the Order Date column into dates by using the DATEVALUE function in the VBA code.

Step-01:
➤ Follow Step-01 of Method-1
➤ Type the following code

Sub datefromstring6()

Dim i As Integer

For i = 5 To 11
    Cells(i, 4).value = Format(DateValue(Cells(i, 3).value), "MM/DD/YYYY")
Next i

End Sub

Here, we have declared i as Integer and assigned i to a value 5 to 11 (within FOR loop)

The FOR loop will continue the operation of this conversion for rows 5 to 11 of Column C and will give the output values in Column D and DATEVALUE will convert the strings into date and after that FORMAT will convert it into the desired format “MM/DD/YYYY”.

➤ Press F5

Result:
Then, you will get the dates converted from the text strings in the Real Date column.

Read More: How to Convert String to Number in Excel VBA

Method-7: Converting a Range of Strings to Date and Time

Here, we have some strings in the Order Time column and we will convert these strings into date and time by using a VBA code and have the outputs in the Real Time column.

Step-01:
➤ Follow Step-01 of Method-1
➤ Type the following code

Public Function datetimecomb(value)

Dim rslt
Dim d
Dim t

If Len(value) = 12 Then

    d = DateSerial(CInt(Left(value, 2)), CInt(Mid(value, 3, 2)), _
    CInt(Mid(value, 5, 2)))
    t = TimeSerial(CInt(Mid(value, 7, 2)), CInt(Mid(value, 9, 2)), _
    CInt(Right(value, 2)))
    rslt = CDate(CStr(d) + " " + CStr(t))

ElseIf Len(value) = 14 Then

    d = DateSerial(CInt(Left(value, 4)), CInt(Mid(value, 5, 2)), _
    CInt(Mid(value, 7, 2)))
    t = TimeSerial(CInt(Mid(value, 9, 2)), CInt(Mid(value, 11, 2)), _
    CInt(Right(value, 2)))
    rslt = CDate(CStr(d) + " " + CStr(t))

Else

    MsgBox "Invalid Format"

    End

End If

datetimecomb = rslt

End Function

This code will create a function named datetimecomb.

Here, CINT will convert the value into an integer and CSTR will convert the value into a string and finally CDATE will convert the string into a date.

DATESERIAL function creates a date by extracting the year, month, and date from the string by using the LEFT and MID functions and, similarly, we have used the TIMESERIAL function to create a time by extracting the hour, minute, and second from the string by using the MID and RIGHT function.

The first IF loop will do the conversion for a 12 number digit (yymmddhhmmss), otherwise, the second loop will do the work for a 14 number digit (yyyymmddhhmmss). For other cases, it will return a message “Invalid Format”

➤Then save the code and return to the worksheet

➤ After that, we have typed the created function name in cell D5

➤ Press ENTER and drag down the Fill Handle tool.

Result:
After that, you will get the dates and times converted from the strings in the Real Time column.

Things to Remember

🔺 The date format after using the CDATE function depends on the system date format

🔺 Dates are stored as a serial number in Excel, so formatting is required to get the final results.

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Download Workbook

Conclusion

In this article, I tried to cover the easiest ways to convert string to date using VBA in Excel effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.

Further Readings

• How to Convert String to Double in Excel VBA
• How to Convert Text to Number in Excel with VBA
• How to Convert String to Long Using VBA in Excel