Excel dates are usually stored as numbers, not as text: a date is a serial value that Excel displays using a date format. However, dates imported or entered in certain ways can remain text, which can stop sorting, calculations, and date formulas from working as expected. To check a cell, use =ISNUMBER(A2) for a date value or =ISTEXT(A2) for text, replacing A2 with the cell you want to inspect.
What It Means for a Date to Be Text
A cell can look like a date without containing an actual Excel date value. For example, the display 3/15/2024 might represent a numeric date, or it might be the text characters 3, slash, 1, 5, slash, and so on. The visual appearance alone does not tell you which one it is.
Excel can format a number as a date, but changing a text cell’s number format does not necessarily convert its contents. If text is left-aligned while nearby dates are right-aligned, that can be a clue, but alignment is not a reliable test because users can change alignment manually. A formula test or a calculation is more dependable.
Text dates often arise when data comes from a CSV file, website, database, or another application. They can also result from typing an apostrophe before a date or importing dates written in a format Excel does not recognize under the current regional settings. A date-looking entry with extra spaces or an unexpected character may also be text.
How Excel Represents Real Dates
Excel stores a date as a serial number: the whole-number portion counts days from a starting point, and the cell’s number format tells Excel to display that number as a date. In the standard 1900 date system used by most Windows workbooks, January 1, 2024 has serial value 45292. March 15, 2024 has value 45366. Applying a date format changes the display, not the underlying number.
This numeric representation is why dates can be added, subtracted, sorted chronologically, and used in functions such as YEAR or MONTH. If A2 contains March 15 and A3 contains March 18 as real dates, subtracting the earlier date from the later one gives 3 days. A time is stored as a fraction of a day, so noon is represented by one-half of a day. A cell formatted to show both a date and time can therefore contain a whole number plus a decimal fraction.
Excel workbooks can use either the 1900 or 1904 date system. The 1904 system is associated with older Mac workbooks, and the same date has a serial value 1,462 days different between the two systems. Excel generally handles the setting within a workbook, but copying raw serial values between workbooks that use different systems can cause dates to shift. This is one reason to transfer date values rather than assume a serial number means the same calendar day everywhere.
How to Check Whether a Cell Is a Date or Text
Excel does not have a separate underlying data type called a date in the same sense as a database date field. For ordinary worksheet cells, a real date is a number with a date display format. Use these checks to distinguish numeric dates from text:
- With the value to inspect in A2,
=ISNUMBER(A2)returns TRUE if A2 contains a numeric value, including a real Excel date. For a cell containing March 15, 2024 as a real date, the result is TRUE. - With the value to inspect in A2,
=ISTEXT(A2)returns TRUE if A2 contains text. For the characters 3/15/2024 stored as text, the result is TRUE. - Select the cell and temporarily apply General number formatting from Home > Number Format. A real date will show its serial number; a text date will continue to display as text. Restore a date format afterward if needed.
- Try a simple date calculation in a spare cell. If subtracting one supposed date from another produces a number of days, both may be numeric dates. An error or unexpected result can indicate text, though a successful calculation is not the only test you should use.
These tests tell you whether a cell is numeric or text, not whether a numeric value is a sensible calendar date. For example, a numeric value could be an invoice number, and a number formatted as a date can display an unintended calendar day. Check both the cell’s contents and the meaning of the column.
Ways to Convert Text Dates into Excel Dates
Choose a conversion method based on how consistent the source text is. Before changing a large dataset, copy the column or workbook so that you can compare the converted results with the originals. Then check several rows, especially dates near the start of a month where day and month order can be confused.
If Excel recognizes the text under your regional settings, DATEVALUE can convert it. With A2 containing the text 3/15/2024 under a U.S. month/day/year setting, enter =DATEVALUE(A2) in another cell. The result is the numeric serial 45366, which displays as March 15, 2024 when the result cell is formatted as a date. DATEVALUE may not interpret the same text consistently under every locale; an ambiguous value such as 04/05/2024 could mean April 5 or May 4.
For a consistent ISO-style text value such as 2024-03-15, you can construct the date from its year, month, and day components. With that exact text in A2, enter =DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)). The result is March 15, 2024, serial 45366 in the 1900 date system. This approach assumes the text always follows the same four-digit-year, two-digit-month, two-digit-day pattern; do not apply it unchanged to other layouts.
After confirming the formula results, format the converted cells as dates. If you need to replace the original text, copy the converted results and use Paste Special > Values so the cells contain the calculated date values rather than formulas tied to the source column. Keep the original column until you have verified the conversion.
Converting a Whole Column with Text to Columns
For a column of consistent date strings, Excel’s Text to Columns wizard can convert values without leaving formulas in the final column. In Microsoft 365 for Windows, select the cells, then choose Data > Text to Columns. In the wizard, choose Delimited and select Next, leave the delimiters unselected unless your data actually requires them, and select Next. Under Column data format, choose Date and the order that matches the source, such as MDY for month, day, year. Select Finish.
The order matters. If the source has 03/04/2024, selecting MDY means March 4; selecting DMY means April 3. Confirm the source’s intended convention before converting. For values such as 25/04/2024, a month/day interpretation is impossible, which can help reveal that the source uses day/month/year. The wizard may be available on Mac as well, though the dialog appearance can vary by Excel release.
Text to Columns can overwrite the selected cells, so preserve a copy first. After conversion, check a few values by changing their number format to General and confirm that they show numbers rather than unchanged date text. If dates come from a recurring CSV workflow, importing and setting the column type deliberately may be more reliable than fixing the worksheet after every import.
Why Text Dates Cause Spreadsheet Problems
Text dates do not behave like numeric dates in many ordinary operations. A sort can place them in an order based on characters rather than calendar sequence. For example, text strings in month/day/year form may sort by their first characters, putting dates from one month together instead of sorting by year and day. A formula that calculates elapsed days may return an error or treat the text as zero, depending on the formula and how Excel interprets the value.
Text can also make filters, date grouping in PivotTables, and date-based rules harder to use. A PivotTable may show individual text labels rather than grouping dates by month or year. A formula such as YEAR(A2) expects a date serial or a value it can interpret; text that looks like a date is not guaranteed to work. When results look inconsistent, check whether the column contains a mixture of numeric dates and text dates.
Do not assume that changing the cells to a date format repairs these problems. Number formatting changes how numeric values appear; it does not reliably parse arbitrary text. Convert the content first, then apply a suitable display format.
Common Causes and How to Prevent Them
Imports are a frequent source of mixed date types. A CSV file contains text characters and does not preserve Excel’s cell formats, so Excel must infer what each field means when opening or importing it. A date such as 2024-03-15 is less ambiguous than 03/04/2024, but an application or locale can still affect how a column is interpreted.
- Use a consistent, unambiguous source format where possible, such as year-month-day, and confirm that the receiving system recognizes it.
- When importing a CSV, choose Data > From Text/CSV and inspect the preview rather than assuming every column was interpreted correctly. In Power Query, set the date column’s type deliberately; if the source locale matters, use Transform > Data Type > Using Locale in the editor.
- Check for leading or trailing spaces, apostrophes, and inconsistent separators in imported text. These can prevent otherwise familiar-looking values from converting.
- Keep a single date convention in each column. Mixing month/day/year and day/month/year values can produce plausible but incorrect dates, which are harder to catch than obvious errors.
For recurring work, document the source convention and test the import with dates that distinguish the month and day, such as the 13th day of a month. A sample containing only dates like 03/04 may not reveal which order the source uses.
Frequently Asked Questions
Why does Excel show a number instead of a date?
The cell probably contains a numeric date serial but is formatted as General or Number. Select it and choose a date option from Home > Number Format. If the displayed value does not become the expected date, confirm that the number is actually intended to be a date serial.
Why did changing the cell format not convert my text date?
Number formats control how numeric values appear; they do not automatically turn every text string into a date. Convert the text with DATEVALUE, a component-based DATE formula, or Text to Columns, then apply a date format to the resulting numeric values.
How can I tell if a whole date column contains text?
Test representative cells with ISNUMBER and ISTEXT, or fill a check formula down a spare column. Look for mixed results, because one imported column can contain both real dates and text. Also verify the meaning of the values rather than relying on cell alignment or appearance.
Can I sort text dates chronologically?
Not reliably for every text format. A year-month-day text layout may happen to sort chronologically as text when every value is consistently padded, but other layouts often sort by character order. Converting the entries to numeric Excel dates is the safer approach.
Does Excel store time with the date?
Yes. Excel represents time as a fraction of a day, so a date and time can share one numeric value. A date-only format may hide the fraction, while a time format may hide the whole-number date portion. The displayed format does not remove either part of the value.
Related
- Combine Multiple XML Files Into One Excel Workbook
- Combine Excel Files: Practical Methods and Steps
- What Column Number Is V in Excel? Find It in Seconds
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- VLOOKUP Table Array Not Working? Fix the RangeIf your VLOOKUP table array is not working, check that the lookup value is in the first column…
- Excel Freeze First Column Not Working? Causes and FixesIf Excel’s first column will not stay visible while you scroll, choose View > Freeze Panes > Freeze…
- Excel Flash Fill: How to Force It and Fix Common IssuesTo force Flash Fill in Excel for Microsoft 365 on Windows, type one example of the result beside…
- Microsoft Outlook Not Working Today? Check Status & FixesMicrosoft Outlook may be down today, but I can’t verify live service status from here; check Microsoft’s service-health…
- Excel Error Symbols: What They Mean and How to Fix ThemExcel’s error symbols are values such as #VALUE! and #DIV/0! that explain why a formula cannot return a…
- Excel Freeze Panes Not Working? Fix the ViewIf Excel Freeze Panes is not working, switch to Normal view, select the cell below and to the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.