If Excel dates are stored as text, convert them with Data > Text to Columns or a formula such as DATEVALUE; changing the cell’s number format alone will not convert text into a date. If you need to keep date-looking entries as text, set the cells to Text before entering or importing them.
What Excel dates as text means
Excel normally stores a date as a number representing days from a starting point, then displays that number using a date format. For example, a real date can be displayed as 17-Mar-2024, 3/17/2024, or 2024-03-17. The display changes, but the underlying value remains a number that Excel can sort chronologically and use in date calculations.
A date stored as text is different: its characters may look exactly like a date, but Excel treats them as text. That can prevent date arithmetic, cause chronological sorts to behave unexpectedly, and make date filters or lookups miss matches. A column can also contain a mixture of real dates and text dates, which is especially easy to overlook after copying data from a website or importing a file.
Text dates are not always bad. They can be useful when preserving an original value exactly, such as a date string in a source file, or when a date-like value is actually an identifier. The important distinction is whether you want Excel to perform date operations on the value.
How to tell whether a date is text
First, select a cell and look at the formula bar and the Home tab’s Number Format box. These can offer clues, but neither is a conclusive test: a text value can be displayed in a cell with a date-like format, and alignment can be changed manually. Text often appears left-aligned and numeric dates often appear right-aligned under Excel’s default alignment, but this is only a hint.
For a reliable check, use a worksheet function. With the value to inspect in cell A2, =ISTEXT(A2) returns TRUE if A2 contains text. With the same cell layout, =ISNUMBER(A2) returns TRUE for a genuine Excel date serial number. A date entered as text will normally return TRUE for ISTEXT and FALSE for ISNUMBER. These tests identify the stored type, not whether the text is a valid or sensible date.
Other clues include a green error indicator in the cell, dates that do not respond to date formatting, and a sort that places values in character order rather than calendar order. For example, text values such as 1/10/2024 and 12/2/2023 may sort alphabetically, not by year and month. None of these clues is as definitive as checking the value type.
Convert text dates with Text to Columns
For a column of consistently formatted date strings, Text to Columns is often the quickest no-formula method in Excel for Microsoft 365 on Windows:
- Select the cells containing the text dates. Avoid selecting unrelated columns that should not be changed.
- Choose Data > Text to Columns.
- In the wizard, choose Delimited and select Next, then select Next again. You do not need to choose a delimiter for this conversion.
- Under Column data format, choose Date and select the order that matches the source text, such as MDY, DMY, or YMD.
- Select Finish, then check the results and apply a date display format if needed.
The selected order matters. If the source contains 04/05/2024, Excel cannot infer from those characters alone whether the intended date is April 5 or May 4. Choose the order used by the source data, not simply the regional order you usually use. Make a copy of the source column first if you need to retain the original text.
In Excel for Mac, Text to Columns is also available from the Data tab, though the wizard’s appearance can differ by release. Excel for the web does not always offer the same full conversion workflow as the Windows desktop app; if the command is unavailable, open the workbook in desktop Excel or use a formula-based conversion.
Convert with DATEVALUE or DATE
DATEVALUE converts a text string that Excel recognizes as a date into a date serial number. With a text date in A2 containing 3/17/2024 and a system that interprets that pattern as month/day/year, enter =DATEVALUE(A2) in another cell; the result is the serial number 45368 in Excel’s standard 1900 date system. Format the result cell as a date to display it as a calendar date.
DATEVALUE depends on the date text being recognizable under the computer’s regional settings. A string such as 04/05/2024 is ambiguous, so the same text may be read differently in different regional configurations. If a formula returns an error, check the source pattern and the system’s expected date order rather than assuming the cell formatting is the problem.
For a consistently structured ISO-style string in year-month-day order, building a date from its parts avoids relying on ambiguous slash-date interpretation. With A2 containing the exact text 2024-03-17, enter =DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)); it returns the date 17 March 2024, serial 45368 in the standard 1900 date system. Format the result as a date, then copy and use Paste Special > Values if you need to replace the original text with fixed date values.
This formula assumes every entry has the same ten-character pattern, with four year digits, two month digits, and two day digits separated by hyphens. It is not a general-purpose parser for inconsistent strings, timestamps, or values with extra spaces. Clean or inspect irregular input before applying it to a whole column.
Choose a conversion method for the source
The right method depends on where the values came from and whether their pattern is consistent. Consider these common cases:
- A clean, uniform column: Use Text to Columns and specify the source date order. It converts the selected values in place.
- A formula-friendly workflow: Use DATEVALUE for recognizable text, or DATE with extracted year, month, and day parts when the source structure is fixed.
- A CSV or other external file: Import it through Data > From Text/CSV and review the preview and column types before loading. Importing with type information is safer than opening a file and assuming Excel guessed correctly.
- A repeatable data-cleaning process: Use Power Query. Select the relevant column, then choose Transform > Data Type > Using Locale and set the type to Date and the locale matching the source. This is useful when, for example, a file uses day/month/year dates but the computer’s default locale expects month/day/year.
- A value that must remain a literal string: Keep it as text, especially if it is an identifier or an original source value rather than a date for calculations.
Power Query menus can vary slightly across Excel releases, but the Using Locale option is designed to resolve differences between the source’s conventions and the computer’s regional settings. In Excel for Mac and the web, Power Query capabilities and available interface options can differ from the Windows desktop edition, so check that the needed transformation is available before building a process around it.
Formatting a date is not the same as converting it
A common misconception is that selecting a cell and choosing a date format will turn any date-looking text into a date. Number formats control how numeric values are displayed; they do not reinterpret text. If a cell contains the text 2024-03-17, applying a date format may leave it looking unchanged because the underlying value is still text.
After a successful conversion, formatting controls the appearance without changing the date itself. Select the converted cells and choose a format from Home > Number Format, or open Format Cells with Ctrl+1 on Windows. On Mac, use Command+1 to open Format Cells. You can choose a built-in date format or create a custom display such as yyyy-mm-dd. Custom date codes can display the same underlying date in different ways; they do not change its value.
There is also a useful distinction between entering a date as text and converting one that already exists. If you want Excel to preserve a string exactly, format the destination cells as Text before typing or importing it. A leading apostrophe can also force an entry to be treated as text; Excel generally does not display that apostrophe in the cell. If you want calculations, sorting by date, or date-based filtering, store a real date value instead.
Common causes and conversion problems
Text dates commonly arrive through CSV files, copied web tables, exported business systems, or data prepared in a different country. A computer set to month/day/year may not interpret a day/month/year string as intended. Two-digit years, inconsistent separators, leading or trailing spaces, and timestamps appended to a date can also complicate conversion.
Excel workbooks can use either the 1900 or 1904 date system. The systems assign different serial numbers to the same modern date, with a difference of 1,462 days. Excel normally handles dates within a workbook’s selected system, but copying raw serial numbers between workbooks with different systems can shift the displayed date. When a converted result looks thousands of days away from the expected date, check the workbook date system and the source interpretation.
Excel’s 1900 date system also preserves a historical compatibility quirk involving the nonexistent date February 29, 1900. This rarely affects modern records, but very early historical dates may not behave like ordinary Gregorian calendar dates. Excel is generally intended for practical spreadsheet dates rather than a complete historical-calendar system.
If conversion produces errors or unexpected results, check a few representative rows before filling a formula down or changing an entire column. Confirm that the source order is understood, remove unwanted spaces where appropriate, and verify whether time information is present. A converted date may include a time fraction even if the cell format hides it; changing the display format can reveal that the value includes more than a date.
Frequently Asked Questions
Why does Excel show a date as a number?
Excel stores dates as serial numbers and uses a number format to display them as calendar dates. If you see a value such as 45368, apply a date format to show its calendar equivalent; the number itself may already be a valid date.
Why does changing the cell format not fix a text date?
Formatting changes how numeric values appear, but a text string remains text. Convert the value using Text to Columns, DATEVALUE, or an appropriate DATE formula, then apply the desired date format.
Can Excel convert dates in day/month/year order?
Yes. In Text to Columns, choose Date and select DMY for day/month/year source values. For imports or formulas, make sure the source order is explicit, because ambiguous strings such as 04/05/2024 cannot identify their intended order on their own.
How can I keep a date-looking value as text?
Format the cells as Text before entering or importing the values, or use a leading apostrophe when entering a single value. This is appropriate for literal strings and identifiers, but not when you need date arithmetic or chronological sorting.
Will converting a text date change how it looks?
It can. Conversion changes the stored value to a date serial, and Excel applies a date display format. Choose a suitable format afterward; the displayed style can differ from the original text even when the calendar date is correct.
Related
- Checklist Template Excel: Set Up, Track, and Reuse Tasks
- Excel Drop-Down Lists: How to Create and Manage Them
- How to Insert a Checkbox in Excel: Steps and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Returns Formula Instead of Value? Fix ItIf Excel displays a formula such as =A2+B2 in the cell instead of its result, first turn off…
- Excel Spill Error: What It Means and How to Fix It QuicklyIn Microsoft Excel for Microsoft 365, a #SPILL! error means a formula is trying to return multiple results,…
- Clear Filter Not Working in Excel? Fix the CauseIf Clear Filter is not bringing every row back in Excel, first choose Data > Sort & Filter…
- How to Fix Zoom in Excel and Restore a Readable ViewTo fix zoom in Excel for Microsoft 365 on Windows, open the View tab and choose Zoom to…
- Excel IF 0 Formula: How to Write It & Fix ErrorsThe direct answer: there is no single “IF 0” formula in Excel. What people usually mean is one…
- Excel Page Break Won’t Remove? How to Fix ItIf an Excel page break won’t remove, first check whether it is a manual break or an automatic…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.