How to Convert Text to Date with Excel VBA – 5 Methods

The sample dataset contains text values.

Method 1 -Using the CDATE Function to Convert Text to Date with Excel VBA

1.1 Convert a Text String to a Date

Step 1:

• Press Alt + F11 to open the VBA Macro.
• Click Insert.
• Select Module.

Step 2:

• Enter the following VBA code.

Sub Convert_Text_String_to_Date_1()
  Dim i As String
  i = "08-13"
  MsgBox i
End Sub

Step 3:

• Save the code and press F5 to run it.
• A message box will display the text value.

Step 4:

• To convert the text, use the following VBA code.

Sub Convert_Text_String_to_Date_2()
'Declare i as a variable
  Dim i As String
  'Define the value of i
  i = "8-13"
  'Insert the CDATE function in a message box
  MsgBox CDate(i)
End Sub

Step 5:

• Save the code and press F5 to run it.

This is the output.

Read More: How to Convert Date from String Using VBA

1.2 Convert a Text Serial Number to a Date

Step 1:

• Insert a new Module.
• Enter the following VBA code.

Sub Convert_Text_Serial_Number_to_Date_1()
'Declare i as a variable
  Dim i As String
  'Define i as a text serial number
  i = 43599
  'Insert a Message box
  MsgBox i
End Sub

Step 2:

• A message box will display the text value.

Step 3:

• To apply the CDATE function, use the following VBA code.

Sub Convert_Text_Serial_Number_to_Date_2()
'Declare i as a variable
  Dim i As String
  'Define i as a text serial number
  i = 43599
  'Apply the CDATE function and show in a message box
  MsgBox CDate(i)
End Sub

Step 4:

This is the output.

Method 2 – Convert Text to a Desired Date Format with Excel VBA

Step 1:

• Insert a new Module.
• Enter the following VBA code.

Sub Desired_Date_Format()
'Declare i as a string variable
  Dim i As String
'Declare Format_Date as a Date variable
  Dim Format_Date As Date
'Define the value of i
  i = 45566
'Apply the CDATE function
  Format_Date = CDate(i)
'Enter Format in a message box
  MsgBox Format(Format_Date, "DD-MMM-YYYY")
End Sub

Step 2:

• Save the code and press F5 to run it.

This is the output.

Method 3 – Applying the DATEVALUE Function to Convert Text to Date with Excel VBA

.

Step 1:

• Enter the following VBA code.

Sub apply_DateValue_function()
Dim Convert_to_Date As Date
'Apply the DATEVALUE function
Convert_to_Date = DateValue(Range("B5").Value)
'Insert a message box to show the date
MsgBox Convert_to_Date
End Sub

Step 2:

• Save the code and press F5 to run it.

This is the output.

Method 4 – Converting a Text to a Date in a Range with Excel VBA

Step 1:

• Create a new Module,
• Enter the following VBA code.

Sub Text_To_Date_Range()
Dim i As Long
 'Apply For loop
For i = 5 To 11
 'Loop starts from 5th row and ends at 11th row
 '3 and 2 are the column numbers
   Cells(i, 3).Value = CDate(Cells(i, 2).Value)
Next i
End Sub 

Step 2:

• Save the code and press F5 to run it.

This is the output.

Method 5 – Converting a Text to a Date in a Specific Cell Selection

Step 1:

• Select B6.

Step 2:

• Press Alt + F11 to open a Macro.
• Enter the following VBA code.

Sub active_cell_convert_to_date()
'Declares the variable as range
Dim myCell As Range
'Set active cell to the variable
Set myCell = ActiveCell
'Enter value in the active cell
'Apply the CDATE function
myCell.Value = CDate(myCell.Value)
End Sub

Step 3:

• Save the code and press F5 to run it.

This is the output.

Download Practice WForkbook

Download the practice workbook.

Related Articles

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