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.

Download Practice Workbook

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

5 Effective Ways to Convert Text to Date with Excel VBA

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 DATEVALUE functions in VBA. 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 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 the Insert
• Select the Module

Step 2:

• Paste the following VBA

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

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.

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

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

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

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.

Read More: How to Change Date Format in Pivot Table in Excel

Similar Readings

• How to Convert Date to Year in Excel (3 Quick Ways)
• Formula for Business Days in Excel (3 Examples)
• How to Convert Date to Day of Week in Excel (8 Methods)
• Get the First Day of the Current Month in Excel (3 Methods)
• How to Convert Date to Day of Year in Excel (4 Methods)

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.

Read More: How to Use Formula to Change Date Format in Excel (5 Methods)

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.

Read More: How to Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel

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

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.

Read More: How to Change Default Date Format from US to UK in Excel (3 Ways)

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.

The ExcelDemy team will respond as quickly as possible.

Stay with us and keep learning.

Related Articles

• How to Convert Date to Day in Excel (7 Quick Ways)
• Remove Time from Date in Pivot Table in Excel (A Step by Step Analysis)
• How to Convert Date to Month in Excel (6 Easy Methods)
• Excel Formula for Current Month and Year (3 Examples)
• How to Get First Day of Month from Month Name in Excel (3 Ways)
• Fix Excel Date Not Formatting Correctly (8 Quick Solutions)
• How to Convert 7 Digit Julian Date to Calendar Date in Excel (3 Ways)