ExcelInsider Team
Converting strings to dates is a common task in Excel VBA, especially when working with data imported from text files or external sources. Getting the date conversion right helps ensure your macros handle dates properly without errors.
In this article, we’ll cover four easy and effective methods to convert strings to dates using VBA, including using CDate, DateSerial, standardizing delimiters, and handling date-time strings. You’ll get simple examples to help you apply each method in your own projects.
Steps to convert standard date strings to actual Date values in Excel VBA using CDate:
➤ Press Alt + F11 , insert a new module, and paste this code:
Sub ConvertStringsToDates_CDate()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim strDate As String
Dim actualDate As Date
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change sheet name if needed
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow
strDate = ws.Cells(i, "A").Value
If Trim(strDate) <> "" Then
actualDate = CDate(strDate)
ws.Cells(i, "C").Value = actualDate
End If
Next i
End Sub➤ The macro finds the last row with data in column A.
➤ It loops through each cell from row 2 down, converting strings to Date using CDate.
➤ Converted Date values are written into column C for easy comparison.
➤ Run the macro in Excel to see date conversions next to your original data.
Use CDate to Convert Standard Date Strings to Dates in Excel VBA
If your data contains date strings in a standard format like “2025-07-10”, you can use VBA’s CDate function to quickly convert these strings into actual Date values.
For this demonstration, assume your worksheet has multiple date strings starting from cell A2 downwards.
The macro will loop through each date string in column A, convert it to a Date using CDate, and write the converted date into column C so you can confirm the conversion visually. We have applied a formula in Column D to verify if the conversion was a success or not.
Steps:
➤ Press Alt + F11 to open the VBA Editor, then choose Insert >> Module.
➤ Paste the following code:
Sub ConvertStringsToDates_CDate()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim strDate As String
Dim actualDate As Date
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change sheet name if necessary
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow
strDate = ws.Cells(i, "A").Value
If Trim(strDate) <> "" Then
actualDate = CDate(strDate)
ws.Cells(i, "C").Value = actualDate
End If
Next i
End Sub
➧ It loops through each date string in column A (starting from row 2).
➧ CDate converts each string into a Date value.
➧ The converted dates are output into column C beside the original strings, preserving column B for notes or other data.
➤ Return to Excel and press F5 to run the macro.
➤ Column C will now show all dates converted properly, formatted according to your system’s default date settings.
Transform Strings to Dates by Parsing Components with DateSerial
Sometimes your date strings have formats or delimiters that CDate can’t reliably convert, such as when the day, month, and year are in nonstandard order or separated by uncommon characters. In these cases, you can split the string into parts and rebuild a Date using VBA’s DateSerial function.
The macro will loop through each row, split the date string by its delimiter, and create a proper date with DateSerial. The results go into column C next to each string.
Steps:
➤ Open the VBA Editor (Alt + F11), Insert >> Module, then paste this code:
Sub ConvertAllStrings_DateSerial()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim strDate As String
Dim dateParts() As String
Dim dt As Date
Dim delimiter As String
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow
strDate = Trim(ws.Cells(i, "A").Value)
' Skip empty cells
If strDate = "" Then GoTo SkipRow
' Determine delimiter
If InStr(strDate, "-") > 0 Then
delimiter = "-"
ElseIf InStr(strDate, "/") > 0 Then
delimiter = "/"
ElseIf InStr(strDate, ".") > 0 Then
delimiter = "."
Else
GoTo SkipRow ' Unknown format
End If
dateParts = Split(strDate, delimiter)
If UBound(dateParts) < 2 Then GoTo SkipRow ' Not enough parts
On Error GoTo SkipRow ' Trap conversion errors
' Identify format based on delimiter and structure
Select Case delimiter
Case "-"
If Len(dateParts(0)) = 4 Then ' YYYY-MM-DD
dt = DateSerial(CInt(dateParts(0)), CInt(dateParts(1)), CInt(Left(dateParts(2), 2)))
Else ' MM-DD-YYYY
dt = DateSerial(CInt(dateParts(2)), CInt(dateParts(0)), CInt(dateParts(1)))
End If
Case "/"
dt = DateSerial(CInt(dateParts(2)), CInt(dateParts(1)), CInt(dateParts(0)))
Case "."
dt = DateSerial(CInt(dateParts(0)), CInt(dateParts(1)), CInt(dateParts(2)))
End Select
ws.Cells(i, "C").Value = dt
SkipRow:
Err.Clear
Next i
End Sub 
➧ It dynamically detects the delimiter (-, /, or .) and splits the string accordingly.
➧ Based on the delimiter, the code assumes the date component order and uses DateSerial(year, month, day) to build a valid Date.
➤ Return to Excel and press F5 to run the macro.
➤ Check column C to see all the converted dates next to their original strings and notes. This method can only operate on a single type of delimiter. It is not suitable for date strings with multiple delimiters or date strings with time. You can refer to other methods for those.
Normalize Delimiters Before Using CDate for Reliable Date Conversion
When date strings come with inconsistent delimiters (like hyphens, slashes, or dots), the easiest way to ensure CDate converts them properly is to first standardize the delimiter to one format, such as a slash (/). This avoids ambiguity and lets CDate handle the parsing.
Using the same dataset in A2:A5 (dates), with notes in B2:B5, this macro will replace dots and hyphens with slashes, then convert each string to a date with CDate. The results are written into column C.
Steps:
➤ Open the VBA Editor (Alt + F11), Insert >> Module, then paste this code:
Sub ConvertAllStrings_StandardizeDelimiter()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim strDate As String
Dim cleanedDate As String
Dim actualDate As Date
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow
strDate = ws.Cells(i, "A").Value
' Replace dots and hyphens with slashes
cleanedDate = Replace(strDate, ".", "/")
cleanedDate = Replace(cleanedDate, "-", "/")
If IsDate(cleanedDate) Then
actualDate = CDate(cleanedDate)
ws.Cells(i, "C").Value = actualDate
Else
ws.Cells(i, "C").Value = "Invalid Date"
End If
Next i
End Sub 
➧ It replaces every dot . and hyphen - with a slash /, standardizing the delimiter.
➧ The cleaned string is then passed to CDate for conversion.
➧ Dates are output in column C, keeping your notes intact in column B.
➤ Switch back to Excel and press F5 to run the macro.
➤ Confirm the converted dates appear in column C aligned with their original strings and notes.
Frequently Asked Questions
How do I convert a string to a date in VBA?
Use the CDate function to convert a recognizable date string into a Date type. It handles standard formats and returns a date value you can manipulate.
Why does CDate give an error for some date strings?
CDate expects dates in recognizable formats. Unusual delimiters or ambiguous formats may cause errors; standardize or preprocess strings before conversion to avoid this.
Can VBA convert date and time strings together?
Yes, CDate converts strings with both date and time parts, returning a Date variable containing both. Ensure your string format matches Excel’s expected format.
How to convert non-standard date formats in VBA?
Split the string into components using Split, rearrange to a standard format, then convert using DateValue or CDate for successful conversion.
Wrapping Up
Converting strings to dates in Excel VBA is essential for accurate data handling and analysis. Whether your date strings are in standard formats or require preprocessing, VBA provides versatile functions like CDate and DateValue to simplify the task. By standardizing delimiters, handling date-time values, or manually parsing strings, you can ensure reliable conversion across diverse datasets. Apply these methods to automate your workflows and improve your Excel projects.
