We can extract lots of information from a date field in MS Excel. The day of the week, the year of a transaction, are but two examples.
If, for example, you want to see the names of the days for transactions, you can convert the dates to text.
Keeping these things in mind, we’ve developed this tutorial to help you convert dates to text in MS Excel. Using these methods, you can convert dates to a wide range of texts that suit your requirements.
2 Methods to convert date to text in Microsoft Excel:
- Use the TEXT function
- Use the Text to Columns feature
Let’s begin by having a look at these methods.
Method #1: Use the TEXT Function
Extremely useful in the scenarios mentioned above, with various options. The TEXT function allows conversion of a date into a name of the month or a day of the week.
Step #1: Open your Excel spreadsheet
Open an Excel file. If you already have an existing workbook in which you want to perform this exercise, open that.
Step #2: Enter the conversion date(s)
In the worksheet, enter the dates you want to convert to text.
Step #3: Select the cell
Select a cell where you want to see the end results, by clicking on it. That cell can be on the same or a different worksheet. For our example, it’s B2.
Step #4: Type in the formula
The syntax for the TEXT formula is:
=TEXT(value, format_text)
- value refers to the date to be converted
- format_text refers to the format of the text into which the date is to be converted.
The various formats of text that a date can be converted into are:
“d” – Converts the date into the day number without a leading zero
“dd” – Converts the date into the day number with a leading zero
“ddd” – Converts the date into the abbreviated name of the corresponding day of the week; for example, Tue
“dddd” – Converts the date into the full name of the corresponding day of the week; for example, Tuesday.
The same applies to months:
“m” – Converts the date into the month number without a leading zero
“mm” – Converts the date into the month number with a leading zero
“mmm” – Converts the date into the abbreviated name of the corresponding month of the year; for example, Jun
“mmmm” – Converts the date into the full name of the corresponding month; for example, November.
Similarly, “yy” gives the last two digits of the year.
While “yyyy” gives the full four digits of the year to which the date corresponds.
This function can also be used to convert a given date into different formats, like:
“mm/dd/yyyy” – Example: 04/01/2022 (USA’s date format)
“dd/mm/yyyy” – Example: 01/04/2022
“dd-mmm-yy” – Example: 01-Apr-22
“dddd, mmmm d, yyyy” – Example: Friday, April 1, 2022
Alternatively, you can have the date part of the formula.
Instead of using a value which would be a cell number, such as A2, enter the date in the formula itself.
For example, =TEXT(“01-04-2022”,”ddd”)
See further examples below:
Step #5: Press Enter
After entering the formula, press Enter on your keyboard to execute the formula.
As you can see in the screenshot above, the dates are converted into different text formats.
Method #2: Use the Text to Columns Features
This method is less flexible compared to the previous method. However, it is fairly simple as it doesn’t require remembering complex date codes as in Method #1.
Note that the results will simply be a text field that still looks like a date.
Step #1: Open your Excel spreadsheet
Open an Excel file. If you already have an existing workbook in which you want to perform this exercise, open that.
Step #2: Enter the conversion date
In the worksheet, enter the dates you want to convert to text.
Step #3: Select the dates
Click on the starting cell and drag the cursor over the dates to select the dates for conversion. Otherwise, you can use the Shift + Arrow keys to select all the relevant cells.
Step #4: Open the ‘Convert Text to Columns’ Wizard
Click on the Data tab in the main menu.
Click on Text to Columns under the Data Tools section in the Data ribbon.
Step #5: Select the file type
In the Convert Text to Columns Wizard, select the Delimited file type.
Click on the Next button.
Step #6: Next wizard step (2 of 3)
In the next window that opens, ensure that no delimiters are ticked.
Click on the Next button.
Step #7: Select the Column data format
In the window that opens, select the Column data format as Text.
Choose a Destination for the text, by clicking on the arrow at the extreme right of the Destination field.
Your spreadsheet will open. Click on the cell where you want to see the results. In our example, it is B2.
Click on the arrow again to return to the wizard.
Click on the Finish button to complete the conversion.
The output will appear in the destination cells earlier selected.
You will now see that the format is Text, as opposed to Date.
Conclusion
Based on your needs, you can convert dates into different formats or text using the above methods. It is best to go with Method #1 if you’re in need of informative data sets like days and months. But Method #2 can be useful if your end goal is to only change the format of the data.