VBA Date to String Conversion in Excel (6 Methods)

Method 1 – Excel FORMAT Function to Convert a Date Range to Strings with VBA

Case 1.1 – Change the Whole Date Format in Range

Suppose we are using a dataset that contains some product identifiers in column B, their shipping costs in column C, and the delivery date which we are going to convert to strings. We want to change the whole date range.

Steps:

• Insert all the dates using slashes. Excel it will automatically convert them to the date format.

• Right-click on the sheet and go to View Code.

• 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

• To Run the code, press the F5 or play button. This will change the dates to strings.

Case 1.2 – VBA to Convert Date to Strings in a Different Column

We’ll add a column to display the converted dates.

Steps:

• Change the delivery date format. Instead of a slash, we will be using hyphens to change the date to strings.
• The range D5:D10 is still in the date format. We’ll convert it into the general format.

• Right-click on the worksheet.
• Go to View Code.
• Insert this 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

• Run the code. We will see the result in column E. All the dates are in the general format.

Method 2 – Change a Date to a String Using the CLng Function

Steps:

• Go to the Visual Basic editor by right-clicking on the worksheet and selecting View Code.
• Insert the following code.

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

• Press F5 and run the code. We will see the date in a number.

Method 3 – VBA Date to String Conversion with the FORMAT Function

We’ll use the same sample dataset to convert the delivery dates into a string.

Case 3.1 – VBA Date to String Conversion (Year, Month, Day)

Steps:

• Go to the Developer tab and select Visual Basic. This will open the visual basic editor.

• Click the Insert drop-down and select Module. This will insert a new module window.

We can also open the visual basic editor by right-clicking on the sheet from the sheet bar and then going to View Code.

• Insert this 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

• Run the code or press the keyboard shortcut F5.
• We will get a message box with the date in the YYYY-MM-DD format as shown below.

Case 3.2 – VBA Date to String Conversion (Day, Month, Year)

STEPS:

• Use the following code:

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

• Run the code by pressing the F5 or play button.
• We will see the date in (day, month, year) format.

Case 3.3 – VBA Date to String Conversion (Month, Day, Year)

STEPS:

• Insert the following code into a VBA module:

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

• Press F5 or the play button to run the code. This will show the result in the message box.

Method 4 – VBA to Change the Present Date to a String with the NOW Function

Steps:

• Go to View Code by right-clicking on the worksheet name.
• Copy and paste the below VBA code into the module.

VBA Code:

Sub Present_Date_to_String()
Dim sDate As String
sDate = Format(Now(), "DD MMM,YYYY")
MsgBox sDate
End Sub

• Run the code by pressing F5 or clicking the play button.
• You will see the result in the message box.

Method 5 – Turn All Dates to Strings Using VBA in Excel

Steps:

• Go to View Code by right-clicking on the worksheet.

• Insert this VBA code in the module.

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

• Run the code by clicking on the play button or using the keyboard shortcut F5.
• Cells D5 to D10 are reformatted to strings.

Method 6 – Using the Number Format to Change Dates to String by Applying VBA Code

STEPS:

• Right-click on the worksheet and go to View Code.

• Insert this code in the module.

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

• Press the F5 or play button to run the code.
• The delivery dates are converted.

Things to Remember

• If you want to see the full month name, you have to write the month with four characters, like this (DD-MMMM-YYYY) or (MMMM-DD-YYYY) or (YYYY-MMMM-DD).

Download the Practice Workbook