WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to convert date to text in Excel

November 7, 2023
50.6K Views

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.

date cells


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").

CellReference


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

date to text


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.

Code

Example(February 1, 2023)

m

2

mm

02

mmm

Feb

mmmm

February

mmmmm

F

d

1

dd

01

ddd

Wed

dddd

Wednesday

yy

23

yyyy

2023

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:

format_text

Result

dd-mmm-yy

1-Feb-23

mm/dd/yyyy

2/1/2023

m/dd/yy

2/1/2023

dddd-mmmm-yyyy

Wednesday-February-2023

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."

Data tab


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

Delimited tick


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."

text tick


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."

Past special


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

Past as values


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

Part 4 Why Use WPS Office?

WPS Office logo


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.

WPS Office: Use Word, Excel, and PPT for FREE, No Ads.

logo

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.

Code

Example(11:05:00)

h:mm

11:05

h:mm:ss

11:05:00

h:mm:ss AM/PM

11:05:00 AM/PM

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").

Time Function


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.

convert the time


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

a text string


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

Assume the date


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").

Formula to Text


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

date to text


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.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.