Convert date to text in Excel to seamlessly integrate date-formatted data into text-based cells, preserving the original format and making your data more flexible for reporting and analysis. Whether you’re consolidating datasets, preparing reports, or automating document creation, understanding how to convert dates to text is crucial. This guide will take you through the process, providing you with practical tips and clear instructions to effectively change date formats into text strings in Excel. By mastering this technique, you’ll ensure your date data is versatile and fully compatible with your textual analysis, enhancing the coherence and functionality of your spreadsheets.
In this tutorial, you will learn various ways to convert date to text in Excel.
This Tutorial Covers:
-
- Convert Date to Text using Text Function
- Example 1: Converting a Specified Date to Text
- Example 2: Converting Current Date to Text
- Convert Date to Text using Text to Column
- Convert Date to Text using the Copy-Paste Method
- Using VBA
- Convert Date to Text using Text Function
Convert Date to Text in Excel
You’ll discover four techniques in this guide for changing the date in Excel to text:
- Using the Text Function
- Using the Text to Column feature
- Using the Copy-Paste method
- Using VBA
Convert Date to Text using Text Function
When you wish to display a value in a specified format, the TEXT function works well. In this instance, it would be appropriate to present the date—a number—in date format.
Let’s first examine how the text feature functions.
The syntax is as follows:
=TEXT(value, format_text)
It needs two justifications:
- value- The numeric value you want to transform into text. This could be a numeric value, a cell reference to a numeric value, or a numeric value returned by a formula.
- format_text- The format in which you want the number to be displayed. It is necessary to specify the format inside double quotations.
Understanding the several formats that can be used in the Text function is necessary to use it.
There are four components to the format for dates:
- day format
- month format
- year format
- separator
The formats for each part are as follows:
Day Format:
d- The day number is displayed without a leading zero. Thus, 2 will be displayed as 2 and 12 as 12.
dd- The day number is displayed with a leading zero. Therefore, 2 will be displayed as 02 and 12 as 12.
ddd- It displays the day’s name as a three-letter day abbreviation. For instance, it will display Mon if the day is a Monday.
dddd- It displays the day’s complete name. For instance, if it’s Monday, Monday will be displayed.
Month Format:
m- Without a leading zero, the month number is displayed. Thus, 2 will be displayed as 2 and 12 as 12.
mm- The month number is displayed with a leading zero. Therefore, 2 will be displayed as 02 and 12 as 12.
mmm- It displays the month name as a three-letter day’s abbreviation. For instance, it will display August if the month is August.
mmmm- It displays the month’s complete name. For instance, it will display August if the month is August.
Year Format:
yy- It displays the year’s two digits. For instance, it will display 16 if it is 2016.
yyyy- The four-digit year number is displayed. For instance, it will display 2016 if it is 2016.
Separator:
/ (forward slash)- The day, month, and year parts of a date can be separated by a forward slash. As an illustration, if you choose “dd/mmm/yyyy” as the format, it will return a date that looks like this: 31/12/2022.
– (dash)- The day, month, and year components of a date can be separated by a dash. For instance, if “dd-mmm-yyyy” is chosen as the format, the result will be a date with the format 31-12-2022.
Space and comma- Additionally, a format like “dd mmm, yyyy” can be created by combining space and comma. The date would appear in the following format as a result: 31 Dec, 2022.
Let’s look at a few instances of how to convert dates in Excel to text using the TEXT function.
- Example 1: Converting a Specified Date to Text:
Let’s say you have a dataset of employees’ joining dates. In column C, you want to combine them.
How to convert date to text in excel is shown below
Step 1: Enter the following formula in cell C2 to convert date to text.
=A2&”‘s joining date is “&TEXT(B2,”dd-mm-yyyy”)
Step 2: Fill the rest of the cells by using autofill handler. The result looks like below:
Notably, we have utilized the TEXT method to convert the date into text using the provided format rather than using the cell reference that contains the date.
The following are some versions of several formats you can use:
You can experiment with other formats as well and put them together in unique ways.
- Example 2: Converting Current Date to Text:
You can combine the TODAY function and the TEXT function to create text that contains the current date.
How to convert current date to text in excel is shown below
Step 1: Enter the following formula in a blank cell where you want to convert the current date to text.
This could be helpful in dashboards and reports so that the date automatically updates to reflect the current date as soon as the file is viewed (or any changes are made).
Convert Date to Text using Text to Column
If you don’t like using Excel formulas, the Text to Column function is another handy way to easily convert dates to text in Excel.
Consider the following dataset that you want to convert the dates in to text format:
The steps to Convert Date to Text using Text to Column are described below
Step 1: Choose every cell that has dates in it that you want to convert to text.
Step 2: Open “Text to Column” by going to Data > Data Tools.
Step 3: Make sure “Delimited” is chosen in the “Convert Text to Column Wizard” dialog box before clicking “Next >”.
Step 4: Make sure that none of the alternatives are selected in the delimiter options. Click on “Next >” after deselecting the default selection.
Step 5: Choose “Text” from the “Column data format” options, enter the target cell’s name(in this example, cell B2), and press “Finish”.
The dates would be instantly converted to text format in this way.
Note: The dates in the two columns are formatted differently. The formatted output is “dd-mm-yyyy”, as opposed to the original format’s “dd mmm, yyyy”. Keep in mind that the Text to Column tool will always convert dates to the short date format, which on my machine is dd-mm-yyyy. For yours, it might be different).
Convert Date to Text using the Copy-Paste Method
The steps to Convert Date to Text using the Copy-Paste method are described below:
Step 1: Copy the dates that are in the selected cells that you want to convert.
Step 2: Paste it into Notepad once it is open. The dates are instantly transformed to text when you paste them in the notepad.
Step 3: Return to Excel and choose the column you wish to paste these dates into.
With the cells selected, navigate to Home -> Number and select Text from the drop-down menu.
Step 4: Return to the notepad now, copy the dates, and then paste them into Excel. The dates have been transformed into text, as you can see.
Using VBA:
Consider employing a VBA code if you frequently need to convert dates to text; you just need to set a VBA macro code once before you can add it to the Quick Access Toolbar. Now, all you have to do to convert dates to text the next time is choose the dataset and press the macro button in the QAT.
The VBA codes are:
Sub Convert_Date_to_Text()
Dim c As Range
For Each c In Selection
c.Value = “‘” & Format(c.Value, “dd-mm-yyyy”)
Next c
End Sub
This VBA code must be entered into a standard module in the VB Editor.
Where should the VBA code go?
How to convert date to text with VBA is shown below:
This code must be put in a standard module in Excel’s VB editor.
Step 1: Select the cells where dates are stored and want to convert to text. Then press “ALT+F11”. It will open “Microsoft Visual Basic for Applications” dialog box.
Step 2: Double click on your mouse’s left button on the sheet where the dates are stored. Then Paste the VBA code mentioned above as per your situation in the module.
The result looks like below.
You may be interested:
