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.
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.
