Excel: Convert Date to Text Without Losing Format


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:

Excel convert date to text without losing format

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

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

How to Convert Date to Quarter and Year in Excel
How to Compare Dates Without Time in Excel
How to Filter Dates by Month in Excel

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *