In Excel, a date can be displayed as text without changing its underlying date value: apply a date number format, or use the TEXT function when you need a text result. If a date was imported or entered as text, convert it to a real Excel date before relying on date calculations, chronological sorting, or date-based formulas.
How Excel stores dates
Excel normally stores a date as a serial number: a count of days from a starting point in the workbook’s date system. The cell’s number format tells Excel how to show that value. For example, the same underlying date can appear as 11/3/2026, 3-Nov-2026, or Tuesday, November 3, 2026 depending on the format. The displayed characters may look like text, but the cell can still contain a number that Excel recognizes as a date.
This distinction matters because a true date can be added to, subtracted from, sorted chronologically, and used in date functions. A text string that merely looks like a date is just characters until it is converted. A common clue is alignment: numbers and dates often align to the right by default, while text often aligns left. But alignment can be changed, so it is not a dependable test by itself.
Excel workbooks can use different date systems, and older workbooks may use the 1904 date system. That can change the serial number associated with a date, so avoid copying raw serial values between workbooks without checking their settings. For ordinary use, enter and format dates rather than trying to manage their serial numbers directly.
Display a real date as text with TEXT
Use TEXT when the result must actually be a text string—for example, when creating a label, a filename, or a sentence that includes a date. With a date value in A2, enter =TEXT(A2,"yyyy-mm-dd") in another cell. If A2 contains November 3, 2026, the result is the text 2026-11-03. The format code controls the output: mmmm d, yyyy produces a result such as November 3, 2026, while ddd, mmm d produces Tue, Nov 3.
The TEXT result is no longer a date value. It is useful for presentation, but it will not behave like the original date in date arithmetic. Keep the original date in its own column if you need to calculate with it, and create the text version in a separate output column.
In Microsoft 365 for Windows, enter the formula in a blank cell and fill it down as needed. If you want to keep only the formatted text, copy the results and use Home > Paste > Paste Values. This replaces the formulas with their displayed text results. Check that the result is what you need before removing or overwriting the source dates.
Change how a date looks without converting it
If you only want a different appearance, change the cell’s number format instead of using TEXT. Select the date cells and choose Home > Number Format, then select a date style. For a custom display, open Format Cells with Ctrl+1 on Windows (Command+1 on Mac), choose the Number tab, select Custom, and enter a format such as yyyy-mm-dd. This changes the display, not the underlying date value.
For example, a date displayed as 2026-11-03 through a custom number format remains available for date calculations and chronological sorting. This is often the best choice for tables and reports. Custom formats can also make dates easier to read without changing data used by formulas, charts, or PivotTables.
Excel date format codes are not identical to codes used by every other program. In Excel, m represents a month in a date format, and mmmm displays the full month name. The exact appearance can depend on regional settings, including month and day names. If the format does not show what you expect, check the format code and the cell’s underlying value.
Enter or preserve a date as literal text
Sometimes text is intentional. A code such as 2026-11 may represent a month rather than a specific day, or a source system may require a date string in a particular format. To preserve an entry as text, select the cells first, then choose Home > Number Format > Text before typing or pasting the values. This tells Excel to treat subsequent entries as text.
You can also type an apostrophe before a single entry, such as '2026-11-03. Excel uses the apostrophe to mark the entry as text and does not display it in the cell. The apostrophe is not part of the visible date string. This method is handy for an occasional value, while setting the cells to Text is more practical for a whole column.
Changing a cell to Text after a date has already been entered does not turn that existing date into a text string; it generally changes only how Excel formats the value. To change existing values, use a conversion method described below. Also note that a text date may sort alphabetically rather than chronologically. Fixed-width year-month-day text, such as 2026-11-03, sorts in date order when every value uses the same layout, but this does not make it a real date.
Convert text dates into Excel dates
If the text uses a consistent, recognized date layout, DATEVALUE can convert it. With a text date in A2 containing 3/15/2025 and a Windows regional setting that interprets that string as month/day/year, enter =DATEVALUE(A2) in another cell. The result is the Excel date value for March 15, 2025, with serial number 45731 in the standard 1900 date system. Format the result cell as a date to display it as 3/15/2025 or another preferred style.
Date interpretation depends on the text and regional settings. For example, 4/5/2025 could mean April 5 or May 4. Do not assume that Excel can infer the intended order from an ambiguous string. Confirm whether the source uses month/day/year, day/month/year, or year/month/day before converting a column.
For a fixed-width ISO-style string in A2, such as 2025-03-15, you can build the date from its year, month, and day components. Enter =DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)). With that example, the result is the true Excel date March 15, 2025 (serial 45731 in the standard 1900 date system). This approach assumes every value follows exactly the same ten-character yyyy-mm-dd pattern; check for spaces or different layouts first.
For imported columns, another option is Data > Text to Columns. Select the text-date column, choose Data > Text to Columns, select Delimited, and click Next twice without choosing a delimiter. In the final step, select Date and the source order—such as YMD or MDY—then click Finish. Verify the converted results and apply a date format. The available controls can vary slightly by Excel version and platform, so inspect the preview before finishing.
Diagnose common date-as-text problems
When a formula or sort behaves unexpectedly, first establish whether the cells contain dates or text. You can inspect the formula bar, try changing the number format, or check a cell with a simple test. With a possible date in A2, enter =ISNUMBER(A2) in another cell. It returns TRUE if A2 contains a numeric value, including a normal Excel date serial, and FALSE if it contains text. This test does not prove that every numeric value is a valid date, so consider the source data as well.
Common symptoms and checks include:
- Dates will not sort in calendar order: Some entries may be text while others are real dates. Convert the text values, then sort the date column.
- A date formula returns an error: The text may contain spaces, an unsupported format, or a date order Excel cannot interpret. Inspect the original string and confirm its regional convention.
- A cell shows a number instead of a date: The value may be a valid date serial displayed with a General or numeric format. Apply a date format to confirm.
- Changing the format has no effect: The content may be text. A number format does not convert text into a date value.
- Some rows convert and others do not: The source may mix formats, blanks, extra spaces, or invalid dates. Clean or separate the different cases before converting the full column.
After conversion, test a few known dates and compare them with the source. A conversion can produce a valid date that is still the wrong day if Excel interpreted an ambiguous string in the wrong order.
Choose between a date value and date text
Use a real Excel date for records, schedules, deadlines, age calculations, elapsed-time calculations, and data that people may sort or filter by date. A real date is also generally the better choice for charts, PivotTables, and formulas. Choose a display format to control what users see while keeping the date available for calculations.
Use text when the string itself is the required output—for example, when joining a date into a heading or exporting a specifically formatted label. If a date must remain text during data entry, set the destination cells to Text in advance or use a leading apostrophe. If an external system expects a date string, confirm its required format and whether it expects text or an actual date value; those are not interchangeable in every import or export workflow.
One useful rule is to separate stored data from presentation. Keep a clean date column containing real dates, and create a separate text column only when a report or export needs a particular literal layout. This makes later filtering and calculations much less fragile.
Frequently Asked Questions
How do I make Excel show a date as yyyy-mm-dd?
Select the date cells, press Ctrl+1 on Windows or Command+1 on Mac, choose Number > Custom, and enter yyyy-mm-dd. This changes the display while preserving a real date value.
Does formatting a cell as Text convert an existing date?
No. Setting the format to Text affects how new entries are treated; it does not reliably convert an existing date value into a text string. Use TEXT for a text result, or copy and paste values after creating that result.
Why does Excel treat my date as text?
Excel may not recognize the entry because its layout conflicts with your regional date order, it contains extra characters, or the cells were already set to Text. Check the source format and convert the values using a method that matches their actual order.
Can I calculate with dates returned by TEXT?
No. TEXT returns text, even when its output looks exactly like a date. Keep or reference the original date value for calculations, and use the text output only for display or an external format that requires it.
Why do dates sort incorrectly when they look correct?
The column may contain text strings, mixed text and date values, or inconsistent text layouts. Convert the entries to real dates and apply one date format. If the values must stay as text, a consistent year-month-day layout can sort predictably as text, but it still will not support normal date calculations.
Related
- Change Block Capitals to Lowercase in Excel
- Cell in Microsoft Excel: Meaning, Types, and How to Use It
- Count Numbers vs Count in Excel PivotTables
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Arrow Keys Not Working? Fix Scroll Lock and MoreIf Excel’s arrow keys are not moving the active cell, first turn off Scroll Lock: press the Scroll…
- LEFT Excel Formula Not Working? Fix ItIf your LEFT formula is not working, check that the formula starts with an equals sign, refers to…
- Excel Won’t Allow Insert Row? Common Causes and FixesYes—Excel may block a row insertion because the sheet is protected, the new row would push cells or…
- Excel SUMIFS Not Working? Causes and FixesYes—Excel SUMIFS can return zero, the wrong total, or an error when its ranges, criteria, or data types…
- How to Prevent #DIV/0! Errors in ExcelTo prevent a #DIV/0! error in Excel, check that the divisor is not zero before dividing. For example,…
- Average Function in Excel Not Working? Fix the CauseIf Excel’s AVERAGE function is not working, first check that the range contains numbers Excel recognizes and that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.