VBA Date to String Conversion in Excel (6 Methods)

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.


Download Practice Workbook

You can download the workbook and practice with them.


6 Ways with Excel VBA to Convert Date to String

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 as the date format.

Excel FORMAT Function to Convert Date Range to String in VBA

➤ Second, right-click on the sheet and go to View Code.

Excel FORMAT Function to Convert Date Range to String in VBA

➤ 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

Excel FORMAT Function to Convert Date Range to String in VBA

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

Convert VBA Date to string in a Different 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.

Convert VBA Date to string in a Different Column

➤ 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

Excel FORMAT Function to Convert Date Range to String in VBA

➤ After that, Run the code. And we will see the result in column E. All the dates are in the general format now.

Convert VBA Date to string in a Different Column

Read more: How to Format Date with VBA in Excel


2. Change Date to String Using CLng Function

The CLng function in Visual Basic for applications translates an expression to a Long data type. Where 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

Change Date to String Using CLng Function

➤ In the end, press F5 and run the code. We will see the date in a number.

Read more: How to Convert Date from String Using VBA


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 how we can change the date into strings.

VBA Date to String conversion with FORMAT Function


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.

VBA Date to String Conversion (Year, Month, Day)

➤ Now, the visual basic window will appear where we are going to write our VBA codes.

➤ Then, click the Insert drop-down and select Module. This will insert a new module window.

VBA Date to String Conversion (Year, Month, Day)

➤ Or, we can open the visual basic editor by right-clicking on the sheet from the sheet bar and then going to View Code.

VBA Date to String Conversion (Year, Month, Day)

➤ 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, 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 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

VBA Date to String Conversion (Day, Month, Year)

➤ At last, Run the code by pressing the F5 or play button.

➤ Now, we will see the date in (day, month, year) format.

VBA Date to String Conversion (Year, Month, Day)


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

VBA Date to String Conversion (Month, Day, Year)

➤ Finally, press F5 or the play button to run the code. This will show the result in the message box.

VBA Date to String Conversion (Month, Day, Year)

Read more: How to Sort Dates in Excel by Year


Similar Readings


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

VBA to Change Present Date to String

➤ Now, run the code by pressing F5 or clicking the play button.

➤ And finally, we will see the result in the message box.

VBA to Change Present Date to String

Read more: How to Get the Current Date in VBA


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

Turn All Dates to String Using VBA in Excel

➤ 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 as a date format anymore.

Read more: How to Calculate Due Date with Formula in Excel


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.

Turn All Dates to String Using VBA in Excel

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

Read more: How to Insert Current Date in Excel


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

Conclusion

Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Further Readings

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo