The article includes detailed step-by-step instructions on how to create a VBA macro that automates the Text to Columns process and converts text dates into Excel date format. The article also covers some common issues that can arise when working with text dates in Excel, such as inconsistent date formats and data that includes non-date values. We also provide tips on how to handle these issues and ensure that your data is properly formatted.
Download Practice Workbook
Excel VBA Text to Columns Date Format: 2 Suitable Examples
The dataset provided is an attendance sheet for a student named Alan at Collegiate School. The sheet lists the student’s ID number, name, and attendance record for various dates. The attendance column indicates whether the student was present or absent on each date. This dataset could be used to track a student’s attendance over time and to analyze patterns in their attendance.
1. Convert a Number into Date Format Using VBA Text to Columns Operation
In the dataset, we noticed that the date wasn’t in the correct format. They wrote the date without any hyphen and slash symbol. We will apply a VBA code to convert the numbers into proper date format.
1.1 Converting to DD-MM-YYYY Format
We know there are many date formats but we’ll learn here to convert in two major formats. In this section, we’ll convert the numbers into DD-MM-YYYY date format.
- First go to the Developer tab in the ribbon>> Select the Visual Basic tab. The Visual Basic window will be opened.
- After launching the VBA and inserting a module, a window will appear before you like in the image above.
- Paste the below code there-
Code:
Sub NumbertoDateFormat()
'Updated by exceldemy
Dim rng As Range
Set rng = Range("D5:D12") ' Replace with your range
' Convert the date format
rng.TextToColumns Destination:=rng, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, xlDMYFormat))
End Sub- After pasting the code click Run or press F5 on the keyboard to run the code.
- You can find xlDMYFormat if you look through the code. Marked in the image below.
This indicates that the date format will be (dd-mm-yyyy) according to this.
The numbers in the Date column have now been transformed into a proper date format.
Here the date format is (dd-mm-yyyy) [UK date format]
1.2 Converting to MM-DD-YYYY Format
Here, we made a slight change in the dataset in the Date column because of the (mm-dd-yyyy) format. We modified the numbers so that they support the MM-DD-YYYY format.
- First, launch VBA and insert a Module in Microsoft Excel.
- Then paste the following code into the module
Code:
Sub NumbertoDateFormat()
'Updated by exceldemy
Dim rng As Range
Set rng = Range("D5:D12") ' Replace with your range
' Convert the date format
rng.TextToColumns Destination:=rng, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=False, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, xlMDYFormat))
End Sub- After pasting the code click the Run option or press F5 on the keyboard to run the code.
- You can find xlMDYFormat if you look through the code. Marked in the image above.
This indicates that the date format will be (dd-mm-yyyy) according to this.
- You will be able to see the above image after running the code.
Here the date format is (mm-dd-yyyy) [USA format]
Note: If the numbers in the Date column don’t match with the (mm-dd-yyyy) format then you won’t get the proper output.
2. Using VBA Text to Columns Operation to Change Date Format
In this segment, we are going to change the date format. Here, in the dataset, the date format is (mm-dd-yyyy)[USA Format]. So, we are going to change it into (dd-mm-yyyy)[UK Format] by using Excel VBA.
- First of all, Launch VBA and open a module
- A window will appear before you. Paste the below code into that module
Code:
Sub ConvertUSDateToUKDateInRange()
Dim Cell As Range
Dim DateRange As Range
' Change "D5:D12" to the range that you want to convert
Set DateRange = Range("D5:D12")
For Each Cell In DateRange
If IsDate(Cell.Value) Then
Cell.Value = Format(Cell.Value, "dd/mm/yyyy")
End If
Next Cell
End Sub- After that, click the Run option or press F5 on the keyboard to run the code.
- The outcome will look like the below image. Where the date is in UK format now
Frequently Asked Questions
1. How do I format a date in Excel using text to columns?
- Click on the “Text to Columns” button in the “Data Tools” group.
- From the Convert Text to Columns Wizard dialog box, select Delimited, and click Next.
- Uncheck all delimiter boxes and then click Next. In the last step, click Finish after selecting Date under Column data format and selecting the format that best fits your dates.
2. How to convert text to date format in VBA?
In VBA, you can convert text to date format using the CDate function. CDate converts a valid date expression, such as a string, into a Date data type.
3. How to add a date in VBA?
The DateAdd Function allows you to add a date or time interval to a date or time, and it then returns the new date or time. The DateAdd Function’s syntax is
DateAdd(interval, number, date)The “interval” argument specifies the time interval to add, such as “d” for days, “m” for months, or “yyyy” for years. The ”number” argument specifies the number of intervals to add or subtract. The “date” argument is the starting date.
Things to Remember
- Always back up your data before making any changes, especially if you’re working with large datasets.
- Test your VBA macro on a small subset of data before applying it to your entire dataset.
- If you encounter any issues, review the error messages generated by Excel and adjust your VBA code accordingly.
Conclusion
The article highlights the importance of properly formatting data in Excel, particularly when working with text dates that may be imported or copied from other sources. It demonstrates how VBA can be used to simplify this process and save time, by automating the Text to Columns feature and converting text dates into Excel date format with ease. The article is a valuable resource for anyone looking to improve their data formatting skills in Excel using VBA and the Text to Columns feature. The clear and concise instructions, along with the practical examples provided, make it easy to follow along and apply the knowledge gained to real-world scenarios. If you have any queries or suggestions please do comment in the comments section below.
