How to Convert Text to Date with Excel VBA (5 Ways)

The date is one of the most prevalent issues we all have with Excel, and it’s often kept as text elements and gets overlooked at first. Whenever they are obliged to apply that date, we will discover that those values are saved as text and that we have no idea. In this tutorial, we will show how to convert text to date in Excel VBA.

Convert Text to Date with Excel VBA: 5 Effective Ways

We’ll show you five of the most effective techniques to convert text to date in the sections below. To begin, we’ll use the VBA CDATE and VBA DATEVALUE functions. Then we’ll show you how to convert the text values as a whole, in a range, or in a specific selection. In the image below, we have a sample data set featuring some text values, we have to convert these text values into date values.

1. Insert the CDATE Function to Convert Text to Date with Excel VBA

1.1 Convert Text String to Date

In the beginning, we will show you how to open and run a Macro in VBA. At first, we will show you the result in a message box, if we don’t convert the text string to date. Later on, we will show you the converted result of the text to date. To do that, we will apply the VBA CDATE function. Follow the instructions below to do the task.

Step 1:

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

Step 2:

• Paste the following VBA code.

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

Step 3:

• Finally, save the program and press F5 to run.
• Therefore, the message box will show you just the text value as it was before.

Step 4:

• To convert the text, paste 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:

• After saving the program, press F5 to run. Therefore, you will get the converted result in the date value shown in a message box.

Read More: How to Convert Date from String Using VBA

1.2 Convert Text Serial Number to Date

Generally, Excel takes the Text value of a Date as a serial number. To convert them to the Date value, follow the steps below.

Step 1:

• Insert a new Module.
• Then, paste 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:

• After running the program, the message box will show no change as we didn’t convert by applying any function.

Step 3:

• To apply the CDATE function, write 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:

• Now, you will see that the message box delivered the result with a date value.

2. Convert Text to a Desired Date Format with Excel VBA

Let’s say you want to convert the text value in a particular format such as American Date Format or other date formats. Then, you can apply Format in a message box containing a specific date format. For example, we want the DD-MMM-YYYY format. To do so, simply, follow the instructions below.

Step 1:

• First of all, Insert a new Module.
• Secondly, type 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:

• Finally, save and run the program.
• Therefore, the converted Date will be shown in DD-MMM-YYYY.

3. Apply the DATEVALUE Function to Convert Text to Date with Excel VBA

By applying the DATEVALUE function, you can accomplish the same thing. Suppose, we want to convert the text in cell B5 to date format. To know how to do it, follow the outlined steps below.

Step 1:

• Write the following VBA codes in a new Module.

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:

• Finally, save the program.
• Press F5 to run the program.
• As a result, you will obtain the Date value in a message box as shown in the image below.

4. Convert Text to Date for a Range with Excel VBA

In the image below, some text values are shown in the range B5:B11. For instance, you want to convert the entire text values to date values at a time. Simply, follow the instructions below to do so.

Step 1:

• After creating a new Module, paste the following VBA.

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:

• Then, save the program and press F5 to run it.
• Consequently, you will get all the Date values instantly.

5. Convert Text to Date for a Specific Cell Selection

If you want to convert a text to a value randomly by the selection, such as cell B6. Then simply follow the outlined steps below.

Step 1:

• Select cell B6.

Step 2:

• Press Alt + F11 to open a Macro.
• Type 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:

• To run the program, press F5 after saving.
• Therefore, the converted date value will appear in cell B6.

Download Practice WForkbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

Finally, I hope you now know how to convert text to date using Excel VBA. All of these tactics should be performed while your data is being educated and practiced. Look over the practice book and put what you’ve learned to use. Because of your generous support, we are motivated to continue delivering initiatives like these.

Please do not hesitate to contact us if you have any questions. Please let us know what you think in the comments area below.

Stay with us and keep learning.

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 Long Using VBA in Excel
• How to Convert String to Double in Excel VBA