How do I convert a string to a date in Excel VBA?

---

You can use the CDate function in VBA to convert a text string to a date.

Here are two common ways to use this function in practice:

Method 1: Convert String to Date Using Default Format (MM/DD/YYYY)

Sub ConvertStringToDate()

    Dim i As Integer

    For i = 2 To 8
        Range("B" & i) = CDate(Range("A" & i))
    Next i
    
End Sub

This particular macro will convert each string in the range A2:A8 to a date with the default date format of MM/DD/YYYY.

For example, a text string of 2023-04-15 will be converted to a date of 04/15/2023.

Method 2: Convert String to Date Using Custom Format

Sub ConvertStringToDate()

    Dim i As Integer

    For i = 2 To 8
        Range("B" & i) = Format(CDate(Range("A" & i)), "MM.DD.YYYY")
    Next i
    
End Sub

This particular macro will convert each string in the range A2:A8 to a date with a format of MM.DD.YYYY.

For example, a text string of 2023-04-15 will be converted to a date of 04.15.2023.

The following examples show how to use each method in practice with the following column of strings in Excel:

Example 1: Convert String to Date Using Default Format

We can use the following macro to convert each string in column A to a date with the default format of MM/DD/YYY:

Sub ConvertStringToDate()

    Dim i As Integer

    For i = 2 To 8
        Range("B" & i) = CDate(Range("A" & i))
    Next i
    
End Sub

When we run this macro, we receive the following output:

Example 2: Convert String to Date Using Custom Format

We can use the following macro to convert each string in column A to a date with a custom format of MM.DD.YYY:

Sub ConvertStringToDate()

    Dim i As Integer

    For i = 2 To 8
        Range("B" & i) = Format(CDate(Range("A" & i)), "MM.DD.YYYY")
    Next i
    
End Sub

When we run this macro, we receive the following output:

Notice that column B converts each string in column A to a date with a custom format of MM.DD.YYYY.

Feel free to use the VBA Format function to display dates in whatever format you’d like.

Note: You can find the complete documentation for the VBA CDate function .