Change Date Format to Text in Excel: 3 Reliable Ways

To convert an Excel date into text while controlling how it looks, use the TEXT function, such as =TEXT(A2,"yyyy-mm-dd"), then copy the results and paste them as values if you need to replace the formulas. This creates text strings rather than dates that Excel can recalculate or reformat. Simply choosing Text as the cell format does not convert dates already in the worksheet.

That difference matters when you are preparing data for an upload, creating a fixed-format report, or joining dates to text from another system. Excel stores most dates as serial numbers and applies a number format to display them. A text conversion turns the displayed date into characters. The steps below are for Microsoft Excel for Microsoft 365 on Windows; the formula also works in Excel for Mac and Excel for the web.

What You’ll Need

A workbook with dates in cells, such as dates in A2:A100. Confirm that Excel recognizes them as dates rather than text.
An empty column for the converted results. Keeping the original dates temporarily makes it easier to check the conversion and recover from mistakes.
The date style you need, for example yyyy-mm-dd for a year-month-day string or dd/mm/yyyy for a day/month/year string.

Step-by-Step

Check that the source values are dates. Select a date cell and look at the formula bar. A recognized date may appear in a different style there than in the grid, and changing its number format should change how it displays. If it is already text, the formula method may not interpret it as a date; see Troubleshooting before proceeding. If the source includes times, decide whether you need to keep them too.
Choose an empty destination column. If the dates are in column A starting at A2, use B2 for the first result. Add a heading such as Date as text so you can tell the new values apart. Avoid putting the formula in the source cell: it would overwrite the input it needs to read.
Enter a TEXT formula using your required pattern. With the original date in A2 and the result in B2, enter =TEXT(A2,"yyyy-mm-dd"). This returns text such as 2026-04-09 for a date of April 9, 2026. For a day-first string, use =TEXT(A2,"dd/mm/yyyy"), which returns 09/04/2026. The pattern is enclosed in straight double quotation marks, and the formula uses a comma between its arguments.
Check the first result before filling down. Confirm the year, month, and day are in the intended order. This is especially important when a date such as 04/05/2026 could mean April 5 or May 4. The formula follows the date value Excel has already stored; it cannot correct a date that was interpreted incorrectly when it was entered or imported.
Fill the formula down the column. Select the formula cell, then drag its fill handle down to the last row, or double-click the fill handle when the adjacent column has a continuous list of data. You can also copy the formula cell, select the destination range, and paste. Excel adjusts A2 to A3, A4, and so on as it fills. Check a few results near the bottom as well as the first one.
Make the results permanent if needed. Select the converted cells and press Ctrl+C. Right-click the selection and choose Paste Special > Values. This replaces formulas with their current text results, so they no longer depend on the source dates. On Mac, copy the cells and use Home > Paste > Paste Values, or the Paste Special options available in the context menu. In Excel for the web, use the Paste Values option from the paste menu. Keep the original date column until you have verified the pasted results.
Verify that the output is text and save a copy. Select a result cell and check the formula bar: after pasting values, it should show the date characters, not a formula. A quick test is to use =ISTEXT(B2) in a spare cell; with the example layout, it returns TRUE for a text result. Remove the test cell afterward if you do not need it. Save the workbook, and only then delete the original date column if your workflow requires it.

Alternative for a quick one-off: If you only need to enter a single date as text, type an apostrophe before it, such as '2026-04-09, and press Enter. Excel uses the apostrophe to treat the entry as text; it does not display the apostrophe in the cell. This is convenient for a new value, but it is not a practical way to convert a large existing list.

Another route for a whole imported column: You can use Power Query when transforming a recurring import. Select the source range and choose Data > From Table/Range, then set the date column’s data type to Text in the Power Query editor and choose Close & Load. Be deliberate about the format and locale: if the input dates are ambiguous, confirm how they were interpreted before converting. Power Query is useful for repeatable refreshes, while the TEXT formula is usually simpler for a one-time worksheet conversion.

Common Mistakes

Setting the cell format to Text and expecting existing dates to change. On the Home tab, the Number Format menu includes Text, but selecting it only affects how future entries are treated and how values are displayed. Existing date serials remain numbers. Use TEXT to create strings from them instead.
Using a date format that is ambiguous to the recipient. A string such as 03/04/2026 can mean March 4 or April 3 depending on regional conventions. If another system will read the file, confirm its expected order. The ISO-style yyyy-mm-dd format is often easier to interpret consistently, but use the format required by the receiving system.
Deleting the source column before checking the conversion. A formula depends on its original date. Removing that source too early can produce errors or leave you without a reliable way to compare values. Paste values first, inspect the results, and retain a backup if the data matters.
Forgetting that TEXT returns text, not a usable date. Text strings may not work as expected in date arithmetic, date-based charts, or calculations that require real dates. Keep the original dates or make a separate text column for exports and labels.
Using the wrong format tokens. In Excel date patterns, mm represents a two-digit month in a date pattern, while mmm gives an abbreviated month name and mmmm gives the full name. For times, hh:mm displays hours and minutes. A misspelled pattern may produce an unexpected result, so test it in one row first.

Tips

Use a four-digit year. A pattern such as dd-mmm-yyyy produces a clearer string, for example 09-Apr-2026, than a two-digit year that could be unclear in older records.
Preserve time when the source contains it. With a date and time in A2, use a pattern such as yyyy-mm-dd hh:mm. If you use a date-only pattern, the time portion is not included in the resulting text.
Include leading zeroes only when required. d/m/yyyy can produce 9/4/2026, while dd/mm/yyyy produces 09/04/2026. Fixed-width dates are often easier to import into systems that expect a consistent character count.
Keep machine-readable and human-readable versions separate. Use one column for true dates used in calculations and another for formatted text used in an export or report. Label the columns clearly so nobody mistakes the text for a date value.
Remember that text sorting is different. Text dates in year-month-day order, such as 2026-04-09, generally sort chronologically as strings when all values use the same fixed-width pattern. Day-first strings such as 09/04/2026 do not reliably sort chronologically as text. Sort the original date column when possible.

Troubleshooting

The result still looks like a number. Check that the formula is =TEXT(A2,"yyyy-mm-dd") and that A2 is the intended source cell. If you instead changed the destination cell’s number format to Text, that alone does not convert a date. Enter the formula in a separate cell and evaluate its result.

The formula returns an unexpected date. The source may have been interpreted using a different regional date order. For instance, an imported 03/04/2026 may already be stored as March 4 rather than April 3. Compare it with the original file or source system and correct the underlying date value before converting it to text. Changing the TEXT pattern only changes the output appearance; it does not change the date Excel stored.

The result shows a formula error or the formula is displayed literally. Confirm the source cell reference and quotation marks. If the formula itself appears in the grid, the destination may be formatted as Text, or the entry may have a leading apostrophe. Change the destination to General, re-enter the formula, and press Enter. If your Excel settings use semicolons as formula separators, replace the comma with the separator required by those settings.

The output is blank, or some rows fail while others work. Look for blank source cells, text mixed into the date column, or invalid dates. TEXT can format recognized date values, but a text value that only resembles a date may need to be converted to a real date first. Check a failing source cell individually and confirm its contents and regional interpretation rather than applying a broad replacement to the entire column.

Dates change after opening the exported file. Some programs automatically interpret date-like text when a CSV or other file is opened. If the exact characters must be preserved, follow the receiving application’s import process and specify the column as text, or use a file format and import method that retains text values. A CSV file does not preserve Excel cell formatting, and Excel may reinterpret its contents when you open it.

Frequently Asked Questions

Does changing the Number Format to Text convert existing Excel dates?
No. It does not turn an existing date serial into text. Use the TEXT function in another column, then paste the results as values if you need fixed text.

How do I convert dates to text without changing how they look?
First identify the format currently displayed, then use the same pattern in TEXT. For example, if you want a day-first, two-digit style, use =TEXT(A2,"dd/mm/yyyy"). Verify the result because the worksheet’s visible appearance may not make regional date order obvious.

Can I convert a whole column at once?
Yes. Enter the formula in the first result row, fill it down alongside the source data, and use Paste Special > Values to remove formulas if necessary. For recurring data transformations, Power Query can apply a repeatable conversion during refresh.

Will the converted text still work in date calculations?
Not as a date value. TEXT returns characters, so date subtraction, date filtering, and other date operations may not treat the result as a date. Keep the original date column for calculations and use the text column only where formatted text is required.

Can I make a text date that sorts in date order?
A consistent year-month-day pattern such as yyyy-mm-dd sorts more naturally as text than a day-first pattern. However, sorting the original Excel dates is safer when you need dependable chronological sorting or calculations.

Does the method differ in Excel for Mac or the web?
The TEXT formula works in Microsoft 365 for Windows, Mac, and the web. The location of Paste Values can vary slightly by platform: use the paste options on the Home tab or in the context menu, then choose Values. The conversion itself is the same.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.