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.

Sample Data

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

Effective Ways to Convert Text to Date with Excel VBA

Step 2:

  • Paste the following VBA
Sub Convert_Text_String_to_Date_1()
  Dim i As String
  i = "08-13"
  MsgBox i
End Sub

Effective Ways to Convert Text to Date with Excel VBA

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.

Effective Ways to Convert Text to Date with Excel VBA

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

Effective Ways to Convert Text to Date with Excel VBA

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.

Effective Ways to Convert Text to Date with Excel 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
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

Effective Ways to Convert Text to Date with Excel VBA

Step 2:

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

Effective Ways to Convert Text to Date with Excel VBA

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

Effective Ways to Convert Text to Date with Excel VBA

Step 4:

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

Effective Ways to Convert Text to Date with Excel VBA


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

Effective Ways to Convert Text to Date with Excel VBA

Step 2:

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

Effective Ways to Convert Text to Date with Excel VBA


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.

Effective Ways to Convert Text to Date with Excel VBA

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

Effective Ways to Convert Text to Date with Excel VBA

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.

Effective Ways to Convert Text to Date with Excel VBA

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.

Sample Data

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 

Sample Data

Step 2:

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

Sample Data


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.

Sample Data

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

Sample Data

Step 3:

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

Sample Data


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.

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo