How to Convert Date from a String Using VBA – 7 Methods

This is the sample dataset.

Method 1 – Using VBA to Convert Date to a Text String

Step 1:
 Go to Developer Tab >> Visual Basic

In the Visual Basic Editor:

• Select Insert >> Module

A Module will be created.

Step 2:

• Enter the following code.

Sub datefromstring1()

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

End Sub

i is declared as String and assigned to the string “1-10-22”.

CDATE will convert this string into date.

• Press F5

Result:
A Message Box containing the date 1/10/2022 (mm/dd/yyyy) will be displayed.

Method 2 – Converting a Value to a Date Using the CDATE Function

Step 1:

• Follow Step 1 of Method 1.
  
• Enter the following code.

Sub datefromstring2()

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

End Sub

i is declared as String and assigned to a value: 44299.

CDATE will convert this value into a date.

• Press F5

Result:
A Message Box containing the date 4/13/2021 (mm/dd/yyyy) will be displayed.

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

Method 3 – Converting a Date from a String Using the DATEVALUE Function

Step 1:

• Follow Step 1 of Method 1.
• Enter the following code.

Sub datefromstring3()

MsgBox DateValue("1/10/2022")

End Sub

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

• Press F5

Result:
A Message Box containing the date 1/10/2022 (mm/dd/yyyy) will be displayed.

Method 4 – Converting a Date from a String Using the FORMAT Function

Use the FORMAT function in the VBA code.

Step 1:

• Follow Step 1 of Method 1.
• Enter the following code.

Sub datefromstring4()

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

End Sub

i is declared as String and assigned to a value: 44299

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

• Press F5

Result:
A Message Box containing the date 4/13/2021 (mm/dd/yyyy) will be displayed.

Method 5 – Converting a Range of Strings to a Date

Step 1:

• Follow Step 1 of Method 1.
• Enter 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

i is declared as Integer and assigned to a value: 5 to 11 (within FOR loop)

The FOR loop will continue the  conversion in rows 5 to 11 in Column C and return the output values in Column D. CDATE will convert the strings into a date and FORMAT will convert it into “MM/DD/YYYY”.

• Press F5

Result:
Dates are displayed in the Real Date column.

Method 6 – Converting a Range of Strings to a Date Using the DATEVALUE Function

Step 1:

• Follow Step 1 of Method 1.
• Enter 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

i is declared as Integer and assigned to a value: 5 to 11 (within FOR loop)

The FOR loop will continue the conversion in rows 5 to 11 of Column C and return the output values in Column D.  DATEVALUE will convert the strings into a date and FORMAT will convert it into “MM/DD/YYYY”.

• Press F5

Result:
Dates are displayed 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

Step 1:

• Follow Step 1 of Method 1.
• Enter 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: datetimecomb.

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

DATESERIAL function creates a date by extracting the year, month, and day from the string (using the LEFT and MID functions). The TIMESERIAL function returns time by extracting the hour, minute, and second from the string (using the MID and RIGHT functions).

The first IF loop will do the conversion for a 12 number digit (yymmddhhmmss). The second loop will do the conversion for a 14 number digit (yyyymmddhhmmss). Otherwise, it will return “Invalid Format”

• Save the code and go back to the worksheet.
• Enter the function name in D5.

• Press ENTER and drag down the Fill Handle.

Result:
Date and time are displayed 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

Practice here.

Download Workbook

Further Readings

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