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.

VBA date from string


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

VBA date from string

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

for a text string

After that, a Module will be created.

for a text string

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.

for a text string

➤Press F5

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

VBA date from string


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.

for a value

➤ Press F5

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

for a value


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.

using DATEVALUE

➤ Press F5

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

VBA date from string

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

using FORMAT

➤ Press F5

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

VBA date from string

Read more: How to Format Date with VBA in Excel


Similar Readings


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.

VBA date from string

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

CDATE function

➤ Press F5

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

CDATE function


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.

VBA date from string

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

DATEVALUE function

➤ Press F5

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

DATEVALUE function


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.

VBA date from string

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”

conversion to date and time

➤Then save the code and return to the worksheet

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

=datetimecomb(C5)

conversion to date and time

➤ Press ENTER and drag down the Fill Handle tool.

conversion to date and time

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

VBA date from string

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.

practice


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo