Generally, Microsoft Excel performs any task much faster than we do it manually. In this article, we will see how to convert VBA date to string in Excel. VBA in Excel always executes the tasks exactly in the same way without errors. The ability to automate Excel tasks and procedures is the most evident feature of VBA programming. One of the primary characteristics of Excel automation is the ability to automate an operation using VBA, which is used to replace tedious processes with visual controls and macros.
Using Excel VBA to Convert Date to String: 6 Ways
We can change the date to a string manually or use any toolbar or any features. But sometimes we need to know how it works on Excel VBA. If you are a programmer or want to learn to change the date to a string using the VBA macros, then this article is for you. We will look at some different methods to turn the dates into strings.
1. Excel FORMAT Function to Convert Date Range to String in VBA
We have a variety of date formats that are used all around the world, but we usually use the DD/MM/YYYY. For this, we have the FORMAT function in VBA, which is quite useful for converting format dates. The date or number that we enter into the FORMAT function is converted into the format that we specify. We are all familiar with and have undoubtedly used a variety of date formats in our lives, the most popular of which are DD/MM/YYYY. Separators such as the hyphen, slash, dot, brackets, and others are also used to create a date format. So let’s have a look at how the FORMAT function works.
1.1. Change the Whole Date Format in Range
For example, suppose we are using the dataset below which contains some product identifiers in column B, their shipping costs in column C, and the delivery date which we are going to convert to string. We want to change the whole date range.
STEPS:
➤ First, write all the dates using slashes. And it will automatically be converted to the date format.
➤ Second, right-click on the sheet and go to View Code.
➤ After that, copy and paste the VBA code below.
VBA Code:
Sub Date_to_String_in_Worksheet()
For i = 1 To Range("D5:D10").Rows.Count
    For j = 1 To Range("D5:D10").Columns.Count
        Range("D5:D10").Cells(i, j).Value = Application.WorksheetFunction.Text(Range("D5:D10").Cells(i, j).Value, "MM-DD-YYYY")
    Next j
Next i
End Sub
➤ In the end, to Run the code, press the F5 or play button. This will change the date format as a string.
1.2. Convert VBA Date to String in a Different Column
At this time, we will see the date to string conversion in a detailed way, with the converted date in another column. Accordingly, we are going to use the dataset below. Likewise, the previous dataset has only an extra column which is a date string in column E. We want to see our desired result in that column.
STEPS:
➤ First of all, we will change the delivery date format. Instead of slash, we will be using hyphens to change the date to strings.
➤ After that, we can see that the range D5:D10 is still in the date format. So now our goal is to convert it into the general format.
➤ Likewise, for the above methods, right-click on the worksheet. Then, go to View Code.
➤ Now, write down the VBA code.
VBA Code:
Sub Date_to_String_in_Worksheet()
For i = 1 To Range("D5:D10").Rows.Count
    For j = 1 To Range("D5:D10").Columns.Count
        Range("E5:E10").Cells(i, j).Value = Application.WorksheetFunction.Text(Range("D5:D10").Cells(i, j).Value, "MM-DD-YYYY")
    Next j
Next i
End Sub
➤ After that, Run the code. And we will see the result in column E. All the dates are in the general format now.
Read More: Excel VBA to Convert Date and Time to Date Only
2. Change Date to String Using CLng Function
The CLng function in Visual Basic for applications translates an expression to a Long data type. The expression parameter represents the expression to be converted to a Long date. It can be used in Excel as a VBA function. So, let’s change a single date in number.
STEPS:
➤ By the same token as the above methods, go to the Visual Basic editor by right-clicking on the worksheet > Click on View Code.
➤ Now, write down the code here.
VBA Code:
Sub Date_to_Number()
Dim i As Date
i = CDate("2022-01-14")
Dim Num As Long
Num = CLng(i)
MsgBox Num
End Sub
➤ In the end, press F5 and run the code. We will see the date in a number.
3. VBA Date to String Conversion with FORMAT Function
VBA CDATE Function is a date data type conversion function that converts a text or a string data type to a date data type. After the value has been transformed to a date data type, we can start messing around with dates. We also use the FORMAT Function to change the date in a dissimilar manner, we are converting to strings. For this, we are using the same dataset as before. At this time, let’s have a look at how to use the CDATE function to put the date data type. After that, by using the FORMAT function we can change the date into strings.
3.1. VBA Date to String Conversion (Year, Month, Day)
To transform the date to string in VBA, structurally be obligated to go to the visual basic window. For this, we have to follow some steps below.
STEPS:
➤ First, go to the Developer Tab and then select Visual Basic. This will open the visual basic editor.
➤ Now, the visual basic window will appear where we will write our VBA codes.
➤ Then, click the Insert drop-down and select Module. This will insert a new module window.
➤ Or, we can open the visual basic editor by right-clicking on the sheet from the sheet bar and then going to View Code.
➤ After that, just write down the VBA Code.
VBA Code:
Sub Date_to_String()
Dim i As Date, strDate As String
i = CDate("2022-01-14")
sDate = Format(i, "YYYY-MM-DD")
MsgBox sDate
End Sub
➤ Finally, Run the code or press the keyboard shortcut F5 to run the code.
➤ And in the end, we will see in the message box, the date in YYYY-MM-DD format as shown below.
3.2. VBA Date to String Conversion (Day, Month, Year)
Previously, we see that the date has been converted as (year, month, and day) manner. Now we want to convert that as (day, month, year). For this, we will follow the same as before. We just made some changes to our VBA Code.
STEPS:
➤ In the beginning, go to the Developer tab > Visual Basic. Then, the visual basic editor will show up.
➤ Insert > Module. Or, right-click on the sheet and select View Code.
➤ After that, just write down the VBA Code here.
VBA Code:
Sub Date_to_String()
Dim i As Date, strDate As String
i = CDate("2022-01-14")
sDate = Format(i, "DD MMM, YYYY")
MsgBox sDate
End Sub
➤ At last, Run the code by pressing the F5 or play button.
➤ Now, we will see the date in (day, month, year) format.
3.3. VBA Date to String Conversion (Month, Day, Year)
Likewise, in the previous ways, now we are going to convert our date as (month, day, year). For this, we need to follow the steps as before.
STEPS:
➤ Sequentially, go to Developer tab > Visual Basic > Insert > Module. Or, right-click on the sheet > View Code.
➤ After that, write down the VBA Code.
VBA Code:
Sub Date_to_String()
Dim i As Date, strDate As String
i = CDate("2022-01-14")
sDate = Format(i, "MMM DD,YYYY")
MsgBox sDate
End Sub
➤ Finally, press F5 or the play button to run the code. This will show the result in the message box.
4. VBA to Change Present Date to String
At this point, we’d like to convert today’s date to a string or a whole number to reflect the date at this time. For this, we are going to use the NOW function. When a worksheet is changed or opened, the NOW function returns the current date and time, which is updated continually. There are no arguments for the NOW function. By applying a number format to the result returned by NOW, we can convert it to a date or a date with time. We can easily change the date to a string using the VBA macro.
STEPS:
➤ Correspondingly to the earlier methods, go to the View Code by right-clicking on the worksheet.
➤ Then, copy and paste the below VBA code.
VBA Code:
Sub Present_Date_to_String()
Dim sDate As String
sDate = Format(Now(), "DD MMM,YYYY")
MsgBox sDate
End Sub
➤ Now, run the code by pressing F5 or clicking the play button.
➤ And finally, we will see the result in the message box.
5. Turn All Dates to String Using VBA in Excel
We want to convert the dates to strings and also want the strings to look exactly like dates. In the given dataset picture we can see the dates are in date format now we are converting it into the string using VBA code.
STEPS:
➤ First, go to View Code by right-clicking on the worksheet.
➤ After that, write down the VBA code here.
VBA Code:
Sub Change_Date_to_String()
changetoText Selection
End Sub
Sub changetoText(target As Range)
Dim cell As Range
Dim txt As String
For Each cell In target
txt = cell.Text
cell.NumberFormat = "@"
cell.Value2 = txt
Next cell
End Sub
➤ Then, run the code by clicking on the play button or using the keyboard shortcut F5.
➤ And finally, cell D5 to D10 is not showing in a date format anymore.
6. Number Format to Change Dates to String Applying VBA Code
We can change the appearance of numbers, including dates. Using number formats without changing the actual number. The cell value that Excel uses to do computations is unaffected by the number format. In the formula bar, the actual value is shown. As we can in cell D5:D10, the delivery date is in date format. So, now we are going to use the number format in VBA macros to convert dates into strings.
STEPS:
➤ Similar to before, right-click on the worksheet then go to View Code.
➤ Then, write down the code below.
VBA Code:
Sub Date_into_String()
Dim D As Date
D = Date
With Range("D5:D10")
.NumberFormat = "@"
.Value = Format(D, "DD MMM,YYYY")
End With
End Sub
➤ After that, press the F5 or play button to run the code.
➤ Finally, we can see that the delivery date is now been converted.
Things to Remember
- If you want to see the full month name you have to write the month like this (DD-MMMM-YYYY) or (MMMM-DD-YYYY) or (YYYY-MMMM-DD).
Download Practice Workbook
You can download the workbook and practice with them.
Conclusion
Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section.
Get FREE Advanced Excel Exercises with Solutions!
