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.

Download Workbook

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 versions 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).

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).

Read more: How to Use VBA DateValue Function in Excel

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 it 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).

Read more: How to Format Date with VBA in Excel

Similar Readings

• Now and Format Functions in Excel VBA (4 Examples)
• VBA Date to String Conversion in Excel (6 Methods)
• Use Year Function in Excel VBA (5 Suitable Examples)
• How to Use Excel VBA MONTH Function (7 Suitable Examples)

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

Here, we have some strings which 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.

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 function and, similarly, we have used the TIMESERIAL function for creating 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.

Read more: How to Use VBA DateSerial Function in Excel

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.

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

• Date Variable in VBA Codes (7 Uses of Macros with Examples)
• How to Get the Current Date in VBA (3 Ways)
• Use the Day Function in Excel VBA (3 Examples)
• How to Get the Day of Week Using VBA (4 Suitable Methods)