How to convert date to text in Excel

Excel stores and analyzes data of all types. Most of the data consists of dates. Dates are presented in numeric form in excel worksheet by default. Excel users can convert this kind of dates in text manually. This method is applicable for data consisting of limited amount of dates. If the data consists of multiple dates, then it becomes very arduous for excel users to convert it in text.

Excel has solved this problem of the users by installing a unique feature of converting numeric dates in text. This feature includes certain methods and techniques which can easily convert the numeric date present in your data in text form. In this article we will discuss such methods which will enable the users to convert date into text. These methods are applicable for 2016\2019\mac\online versions.

Part 1 Using Text Function

This section of the article focuses on the essential method of converting dates to text in Excel using the powerful TEXT function. Unlock the potential of this function as we guide you through the process of effortlessly transforming date values into user-friendly text representations. Master this technique to efficiently manage your date data in Excel and enhance your data analysis and presentation.

To convert dates to text in Excel using the TEXT function, follow these simple steps:

The syntax of the Excel TEXT function is as follows:

TEXT(value, format_text)

Step 1. Select the cell containing the date you want to convert.

Step 2. In a new cell, use the formula "=TEXT(CellReference, "DesiredFormat")".

Step 3. Replace "CellReference" with the cell containing the date and "DesiredFormat" with the text format you want (e.g., "dd/mm/yyyy" or "mmm dd, yyyy").

Step 4. Press Enter, and the date will now be displayed as text in the specified format.

That's it! The formula successfully converted the date to text in the desired "dd/mm/yyyy" format. “General” means that you change the format of the date successfully.

Pro tips: You can use different text formatting formulas to get different text results, this table below summarizes some of the formats.

Knowing the codes corresponding to different text formats for day, month and year, you can choose to get different results according to your needs.

For example:

You can apply this method to convert other date values to text with different formats as needed.

Part 2 Using Text To Column

Learn a different approach to convert dates to text in Excel through the Text to Columns feature. Follow our step-by-step guide to effortlessly split and format your date values into text representations.

Step 1. Select the cells containing the dates you want to convert.

Step 2. Go to the "Data" tab in Excel's ribbon and click on "Text to Columns."

Step 3. Choose "Delimited" and click "Next."

Step 4. Select the delimiter that separates the dates (e.g., comma or space) or choose "Fixed width" if there is no delimiter. Click "Next."

Step 5. Set the column data format to "Text" and click "Finish."

Step 6. The dates will now be converted to text in the selected cells.

Part 3 Using Copy Paste Method

In this part, we will introduce a simple yet effective Copy Paste method to convert data, including dates, into text. Experience a quick way to achieve your desired text format without compromising on data integrity.

Step 1. Select the data range containing the dates you want to convert.

Step 2. Copy the selected data (Ctrl + C).

Step 3. Right-click on the destination cell where you want to paste the converted data as text.

Step 4. Choose "Paste Special."

Step 5. Select "Values" and click "OK."

Step 6. The data, including dates, will now be converted to text in the destination cell.

Part 4 Why Use WPS Office?

Discover the advantages of using WPS Office for all your office needs, including date-to-text conversions. Explore its affordability, compatibility, rich feature set, and user-friendly interface, making it a preferred choice over other office suites.

We recommend using WPS Office for date-to-text conversions due to the following reasons:

1. Price: WPS Office is cost-effective, making it budget-friendly.

2. Compatibility: It seamlessly works with various document formats.

3. Features: WPS Office offers additional tools like resume assistants and smart forms.

4. User-friendly Interface: Its intuitive design ensures easy navigation.

By opting for WPS Office, you can experience enhanced productivity, efficiency, and creativity in your spreadsheet tasks and beyond. For more information, you can refer to the following references.

FAQs about excel convert date to text

• Can I convert time to text strings?

Yes, you can use the same method to convert time to text strings. All you need to know is just some new formats of time value.

Here is an example:

Step 1: Select an empty cell where you want to display the converted time as text (let's say cell A1).

Step 2: Enter the time value you want to convert into cell A1 in a recognized time format (e.g., "12:30 AM" or "15:45").

Step 3: In another cell (let's say cell B1), use the formula =TEXT(A2,"h:mm AM/PM") or

=TEXT(A2,"dd/mm h:mm AM/PM") or =TEXT(A2,"dd/mm/yy h:mm AM/PM") to convert the time in cell A1 to a text string.

Step 4: Press Enter, and cell B1 will display the time as a text string in the specified format

By applying the TEXT function to time values, you can easily convert them to text strings in your preferred format. Experiment with different time formats in the formula to customize the text representation of the time values as needed.

• Can I convert text back to dates after performing date-to-text conversion?

Once you've performed date-to-text conversion in Excel, you can revert the process and convert the text back to dates. Excel offers useful functions such as DATEVALUE and features like text-to-columns, enabling you to accomplish this task effortlessly. You can find a complete guide and quick tutorials on how to convert text to columns in Excel at WPS Academy.

• Can I combine the date with some other texts when converting to text?

Definitely! You can use “&” to combine your date with other texts after converting to text. Here is an example:

Step 1: Assume the date in cell

Step 2. In a new cell, use the formula "=TEXT(CellReference, "DesiredFormat")".

Step 3. Replace "CellReference" with the cell containing the date and "DesiredFormat" with the text format you want (e.g., "dd/mm/yyyy" or "mmm dd, yyyy").

Step 4. Press Enter, and the date will now be displayed as text in the specified format.

Summary

In this article, we explored simple methods to convert dates to text in Excel, such as using the TEXT function, Text to Columns, and Copy Paste. These techniques enable easy transformation of date data into text representations for better data analysis and presentation. Additionally, we emphasized the advantages of using WPS Office, including its affordability, compatibility, rich features, and user-friendly interface, making it a preferred alternative for date-to-text conversions and overall office needs.