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
=datetimecomb(C5)
➤ 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.