Table of Contents
To convert a date to text in Excel without losing the format, use the TEXT function and specify the date format as desired. For example, =TEXT(A1,”dd-mm-yyyy”) will convert the date in cell A1 to text with the format dd-mm-yyyy. The TEXT function also allows for custom formatting, such as using the “@” symbol to include the time in the conversion.
You can use the following formula to convert a date to text without losing format in Excel:
=TEXT(A2, "dd-mm-yyyy")
This particular formula will convert the date in cell A2 to text while using dd-mm-yyyy as the date format.
Note that by using the TEXT function, you can specify any date format you’d like.
The following example shows how to use this formula in practice.
Example: Convert Date to Text Without Losing Format in Excel
Suppose we have the following list of dates in Excel that currently have a dd-mm-yyyy date format:
Suppose we would like to convert each of these dates to text without losing the format they’re currently in.
To do so, we can type the following formula into cell B2:
=TEXT(A2, "dd-mm-yyyy")
We can then click and drag this formula down to every remaining cell in column B:
Column B displays each date in column A in a dd-mm-yyyy format.
We can verify that each cell in column B is in a text format by typing the following formula into cell B2:
=ISTEXT(B2)
We can then click and drag this formula down to each remaining cell in column B:
Since each cell in column B returns TRUE, this confirms that each value in column B is indeed formatted as text.
Note: You can find the complete documentation for the Excel TEXT function .