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.
- Press Alt + F11 to open the VBA Macro.
- Click on the Insert
- Select the Module
- Paste the following VBA
Sub Convert_Text_String_to_Date_1() Dim i As String i = "08-13" MsgBox i End Sub
- Finally, save the program and press F5 to run.
- Therefore, the message box will show you just the text value as it was before.
- 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
- 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.
- 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
- After running the program, the message box will show no change as we didn’t convert by applying any function.
- 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
- 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.
- 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
- 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.
- 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
- 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.
- 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
- 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.
- Select cell B6.
- 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
- To run the program, press F5 after saving.
- Therefore, the converted date value will appear in cell B6.
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.