What Is Date To Text In Excel?
Date to Text in Excel is a method to convert a date value into text format. And we can perform the conversion using the TEXT(), Text to Columns wizard, VBA code, and Notepad.
Users can change the Dates to Texts in a worksheet to display the date value as a string instead of a number.
For example, the following table contains a list of dates and the descriptions for displaying the dates in the text formats in column C.
Then, considering the above-mentioned Date to Text in Excel definition, we can use the TEXT function in excel in the target cells to achieve the desired results.
Let us see the cell C5 expression to check how it works. The TEXT() helps changing Date to Text in Excel cell C5 from 4/20/2023 to Apr 20. And then, the formula joins the specified texts to return the required text string. Column D is for our reference.
Table of contents
Key Takeaways
- The Date to Text in Excel is a technique to display a date value as a text. Users can use the date as text when the requirement is to show the date as a string in the desired format,
- The dates converted into texts will appear left-aligned, and the Home à Number Format field will not show the data format as Date. Instead, it may show the data format as General or Text.
- The Date to Text conversion in Excel is possible using the TEXT(), Text to Columns wizard, VBA code, and Notepad.
Syntax Of Date To Text In Excel
The Date to Text in Excel formula syntax is as follows:
where both the mandatory arguments are as follows:
- value: The numeric value we require to convert into text.
- format_text: A text string defining the formatting to apply to the supplied value.
Furthermore, we will use the following date format codes when supplying the format_text argument value to the TEXT().
How To Convert Date To Text In Excel?
The steps for changing Date to Text in Excel using the TEXT() are as follows:
- Ensure the date to convert into text is valid and accurate.
- Select a target cell and enter the TEXT() by typing the ‘=’ symbol, the function name TEXT, and an opening bracket. Provide the input date value and the date format code, separated by a comma, as the TEXT() arguments. And type the closing bracket.
And if the output requires showing text along with the date converted into text, use the double quotations and the ‘&’ symbol to concatenate the texts and the TEXT().
- Press Enter to view the date in the required text format.
We will consider an example using the Date to Text in Excel.
The table below contains a list of employees and their last working dates. We will display each employee’s last working date as a comment.
The steps to convert Date to Text in excel are as follows -,
- Select cell E3, and enter the formula =C3&”‘s last working date is “&TEXT(D3,”dd mmmm, yyyy”)
- Press Enter to view the required comment in cell E3.
[Alternatively, select the target cell E3 and enter the required texts to concatenate, followed by the ‘&’ symbol. And then, click Formulas → Text → TEXT to open the Function Arguments window.
In the Function Arguments window, update the date or the cell reference to the date that we have to convert into text in the Value field. Also, update the required date format we wish to see in the resulting text. And click OK to close the window.
- Using the fill handle, drag the formula in the remaining target cells E4:E12, as shown below.
Let us see the cell E12 expression to check how it works. First, the TEXT() returns the input date 2/15/2023 as 15 February, 2023, based on the specified date format. And then, the expression concatenates the mentioned texts with the TEXT() output to give the output highlighted above.
Examples
We will consider some advanced scenarios using the Date to Text in Excel examples.
Example #1 – Using TEXT Function
The table below requires the date to be today’s date. And it shows the date formats in which the specified date value should display when converted into text in column E.
- Step 1: Select the target cell E3, enter the formula =TEXT(TODAY(),D3), and press Enter.
- Step 2: Using the fill handle, update the formula in cells E4:E6.
As we must work with the current date,
- We insert the TODAY function in excel as the first argument in the TEXT().
- And the second argument in the TEXT() is the date format specified in column D.
For example, the TODAY() in cell E6 expression returns the current date. And then, the TEXT() converts the TODAY() output into a text in the date format specified in cell D6.
Thus, we see the abbreviated day (“ddd”), followed by a comma, the short form of the month (“mmm”), and the day number with a leading zero (“dd”).
Example #2 – Using Text to Column
The Text to Columns wizard helps convert Dates to Texts, with the texts appearing as short dates.
For example, the table below shows a list of dates in column C. We will convert the given dates into texts in column D, with the texts in the default short date format.
The steps to use the Text to Columns wizard and achieve the desired results are,
- Step 1: Select cell range C3:C9, and click Data → Text to Columns to open the Text to Columns wizard.
- Step 2: Select Delimited as the file type, and click Next on the wizard’s first page.
- Step 3: The wizard’s second page opens, where we ensure all the Delimiters are unselected. And click Next.
- Step 4: The third page of the wizard opens, where we select Text as the Column data format.
And then, update the reference to the top cell of the new target column in the Destination field, where we must display the date in text format.
Clicking Finish will update the corresponding target cells in column D with the column C dates in text format.
And though the target cell D3 value appears as a short date in the Formula Bar, the Number Format in the Home tab shows the data format as Text.
Example #3 – Using Copy Paste Method
We can use a text editor, such as Notepad, and convert Dates to Texts in the target cells, with the option to choose how the dates in text format should display.
For example, the table below contains a list of dates in column B. We will convert the given dates into texts and display the results in the corresponding column C cells.
The steps to use Notepad to achieve the required outcome are,
- Step 1: First, open a notepad. Then, select cell range B3:B9, press Ctrl + C to copy the date values, and click on the Notepad.
And press Ctrl + V to paste the date values in the Notepad.
- Step 2: Press Ctrl + A to select all the data in the Notepad, and press Ctrl + C to copy the data.
- Step 3: Select the target cell range C3:C9, and right-click to select Format Cells from the context menu to access the Format Cells window. Otherwise, we can select the required cell range, and press the keyboard shortcut Ctrl + 1 to open the Format Cells window.
And in the Number tab in the Format Cells window, select the category as Text.
Finally, clicking OK will close the Format Cells window. And we can view the dates converted into texts in column C.
In the above example, the texts in column C appear to have the same date format as the source data. But the left alignment and the Number Format in the Home tab indicate the data in column C is in the text format.
Example #4 – Using VBA
We shall see how to use the FORMAT function in VBA to change dates into texts.
The table below lists a date in different date formats.
The steps to convert the given dates into texts using Excel VBA are as follows:
- Step 1: With the worksheet containing the above table open, press Alt + F11 to open the VBA Editor.
- Step 2: Select the applicable VBAProject, and choose Module under Insert in the menu.
The new module window will open.
- Step 3: Enter the VBA code, shown below, in the module to convert the given dates into texts.
Sub Date_To_Text()
Dim i As Integer
For i = 2 to 4
Range(“B” & i).Value = Format(Range(“A” & i), “dd mmmm, yyyy”)
Next i
End Sub
- Step 4: Click the play icon to run the code.
Finally, when we open the active worksheet, we will find the target cells updated with the dates in the text format specified in the VBA code.
And the output in the target cells appears left-aligned, with the Home à Number Format set as General, which indicates the date values are texts.
Important Things To Note
- If the given date value is in a cell, then when using the TEXT() to convert the given Date to Text in Excel, ensure to apply the function in another cell, with the source data cell remaining untouched. The reason to do so is that the TEXT() output will make referencing the converted Date to Text difficult in future calculations.
- When using the Text to Columns wizard for date-to-text conversion, uncheck all delimiters on the wizard’s second page.
Frequently Asked Questions (FAQs)
Let us see how to convert long Date to Text in Excel with an example.
Suppose the table below contains a list of dates in column B, with the data format set as Long Date in Home → Number Format.
The steps to convert the long Dates to Texts and display them in column C are,
• Step 1: Select the target cell C3, enter the TEXT() formula =TEXT(B3,”mm-dd-yyyy”), and press Enter.
• Step 2: Implement the formula in the remaining target cells using the fill handle.
The TEXT() accepts the long date as input. And it converts the long date into text in the specified format.
We can get month in text from date in Excel using the formula TEXT(value, Month Format).
For example, the table below shows a list of dates in column D.
The steps to obtain the months in text from the given dates and use the TEXT() with the applicable date format codes in the target cells and achieve the desired outcome are as follows:
• Step 1: Select the target cell E3, enter the TEXT() formula =TEXT(D3,”m”), and press Enter.
• Step 2: Select the target cell E4, enter the TEXT() formula =TEXT(D4,”mm”), and press Enter.
• Step 3: Select the target cell E5, enter the TEXT() formula =TEXT(D5,”mmm”), and press Enter.
• Step 4: Select the target cell E6, enter the TEXT() formula =TEXT(D6,”mmmm”), and press Enter.
• Step 5: Select the target cell E7, enter the TEXT() formula =TEXT(D7,”mmmmm”), and press Enter.
The TEXT() accepts the date value in the column D cell in the specific row. And depending on the specified month format code, the function returns the month as a text in the target cells.
For example, “m” represents the month number without a leading zero, and “mm” indicates the month number with a leading zero. On the other hand, “mmm” gives the short form of the month’s name, and “mmmm” gives the month’s full name. And finally, “mmmmm” represents the first letter in the month’s name.
We can convert a Date to Text in Excel without losing formatting by following the below steps:
1) Copy the dates given in a worksheet and paste them into a Notepad.
2) Return to the worksheet and select the target cells where we must display the dates converted into text without losing formatting. And click Home → Number Format → Text.
3) Copy the dates from the Notepad and paste them into the target cells in Excel.
Download Template
This article must help understand the Date to Text in Excel, with its formula and examples. We can download the template here to use it instantly.
Recommended Articles
This has been a guide to Date To Text In Excel. Here we convert date to text or strings using pre-set numeric codes, examples & downloadable excel template. You can learn more from the following articles –
Leave a Reply