Excel Date Value Not Working? Causes, Checks, and Fixes

If an Excel date value is not working, first check whether the cell contains a real date or text that merely looks like one: select it, choose Home > Number Format > Short Date, and see whether it displays correctly. If it does not, convert the text using Data > Text to Columns and the date order that matches the source; if it is already a date, check the cell format, formula, and workbook date system.

How Excel stores dates

Excel does not store an ordinary date as a month name or a formatted string. In the standard 1900 date system, a date is represented by a number counting days from a starting point. For example, January 1, 2024 is serial number 45292. Time is stored as a decimal fraction of a day: 12 noon is one half, or 0.5. A cell’s number format tells Excel how to display that underlying value.

This separation explains many apparent date failures. A cell can contain a valid date serial but display as a plain number, or it can display text that resembles a date while remaining unusable in date calculations. Formatting changes appearance; it does not necessarily convert text into a real date. A real date can be added to or compared with other dates, while unconverted text may sort alphabetically or trigger errors in formulas.

Excel’s 1900 date system also preserves a historical compatibility quirk: it treats 1900 as if it had a February 29, although that date did not exist. This affects early serial values, not ordinary modern dates. Excel workbooks can also use the 1904 date system, which has a 1,462-day offset from the 1900 system for the same dates. That matters when values are copied between workbooks configured differently.

Check whether the value is a date or text

Start by selecting the cell and looking at the formula bar. If the value appears left-aligned by default, that can be a clue that it is text, while numbers and real dates are commonly right-aligned. Alignment is not conclusive because users can apply their own alignment settings. A more reliable check is to change the format temporarily to General or test the value with a formula.

With the value being checked in A2, enter =ISNUMBER(A2) in another cell. TRUE means A2 contains a number, which includes Excel date serials; FALSE means it is not stored as a number. This does not prove that every number is a valid date, so use it alongside what you know about the data. A large number such as 20240131 is numeric, for example, but Excel does not automatically interpret it as January 31, 2024.

Look for other clues in imported data. A leading apostrophe, spaces before or after the date, nonbreaking spaces, or a date separator different from your regional settings can prevent conversion. A green triangle may appear in a cell flagged as a number stored as text, although Excel does not flag every text date. If the source includes hidden characters, inspect a few cells carefully before converting a whole column.

Fix dates that display as numbers or symbols

If a cell shows a number such as 45292, it may already be a valid date whose format is set to General or Number. Select the cells, then choose Home > Number Format > Short Date. Use the dropdown arrow beside the number format controls to select a date style; choose Long Date if you want a month name and weekday. You can also press Ctrl+1 on Windows, or Command+1 on Mac, to open Format Cells and select a date category.

If a cell shows a row of hash marks, such as ####, the usual cause is that the column is too narrow to display the formatted value. Widen the column by dragging its right boundary or double-clicking that boundary to AutoFit. Hash marks can also appear when a date-time calculation produces a negative date or time that the workbook’s date system cannot display. In that case, investigate the calculation rather than simply widening the column.

Applying a date format will not repair text. If a text value stays unchanged after choosing Short Date, use a conversion method instead. Conversely, do not convert a number that is already the right date serial: changing it unnecessarily can produce a different date or an error.

Convert text dates with Text to Columns

For a column of consistently structured text dates, Text to Columns is a dependable conversion option in Excel for Microsoft 365 on Windows. Make a copy of the original data first, especially if the source mixes date formats. Then select the date cells and follow these steps:

Choose Data > Text to Columns.
Select Delimited and choose Next, then leave the delimiter selections unchanged and choose Next again.
Under Column data format, choose Date and select the order used by the source, such as YMD, MDY, or DMY.
Check the destination so the converted values do not overwrite data you need, then choose Finish.

For example, the text 31/12/2024 is day-month-year. Choosing MDY would misread or fail to convert it; choose DMY. A text value such as 2024-12-31 should be interpreted as YMD. The exact choices matter because Excel may otherwise apply the computer’s regional date conventions. In Excel for Mac, the available Text to Columns controls and layout can differ by release; verify the preview and the selected date order before completing the conversion. Excel for the web has fewer desktop data-import tools, so if the command is unavailable, open the workbook in desktop Excel.

Convert a consistent date format with a formula

A formula can help when the source has a consistent text layout that Excel is not parsing automatically. Suppose A2 contains text in the exact year-month-day pattern 2024-12-31, with four year digits, two month digits, and two day digits. In B2, enter =DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)). For that example, the formula returns a real Excel date for December 31, 2024. Format B2 as Short Date to display it as a date rather than its serial number.

This formula assumes that every value uses the same YYYY-MM-DD pattern and has no extra characters. It is not a universal parser for mixed dates, timestamps, or values such as 12/31/2024. Check several converted results against the source before filling the formula down. When the results are correct, copy the new column and use Paste Special > Values if you need fixed values instead of formulas. Keep the original column until you have confirmed the conversion.

DATEVALUE can also convert recognized date text, but its interpretation depends on the text and regional date settings. With the date text in A2, =DATEVALUE(A2) returns a date serial when Excel recognizes the text. If a value is ambiguous, such as 03/04/2024, different regional settings can interpret it as March 4 or April 3. For data with a known order, specify that order through Text to Columns or parse its year, month, and day components explicitly.

Check regional settings and imported data

Date formats vary across countries. For instance, 04/05/2024 can mean April 5 in month-day-year order or May 4 in day-month-year order. Excel’s recognition of typed and imported date strings can depend on Windows regional settings, the file’s structure, and the import method. If every date in a file is shifted or rejected, verify the source’s documented order before making changes. Do not assume that changing the display format will correct a misread date; it only changes how the stored value appears.

When opening a CSV, Excel may interpret date-looking values automatically. If the file is important or contains ambiguous dates, use the import options in desktop Excel rather than relying on automatic interpretation. In Microsoft 365 on Windows, you can use Data > From Text/CSV to inspect the import before loading it. Check the preview and the detected data types. If the dates are imported as text, choose a conversion approach that matches the source; if they are already dates but wrong, reload from the original file with the correct interpretation rather than trying to repair a whole column blindly.

Also inspect delimiters and spaces. Text such as 2024-12-31 followed by an invisible space may not behave like the clean string shown on screen. A copied value can contain a nonbreaking space or other character. If only some rows fail, compare a working cell with a failing one and check the source system or export settings. Cleaning data without identifying what differs can remove meaningful content or create new errors.

Investigate formulas and date calculations

If a formula returns an unexpected date, check its inputs and the result’s number format. A formula such as A2+7 adds seven days only when A2 contains a real numeric date. If A2 is text, the formula may return an error or behave differently from what you expect. Use the ISNUMBER check on the input cells and convert text values before calculating.

For date differences, subtract the earlier date from the later date to get the number of elapsed days. If the result displays as another calendar date, change the result cell to General or Number. For example, a difference of 30 days displayed in a date format can look like a date in 1900; the arithmetic may be correct, but the output format is unsuitable. If the result is negative, confirm that the dates are in the intended order and consider whether the workbook’s date system can display that result.

Functions that create dates can also normalize out-of-range components. DATE uses numeric year, month, and day arguments; an excessive month or day may roll into a later month rather than produce the intended error. Check that the components extracted from text are in the correct order and that they contain only the expected digits. If dates originate from formulas that return empty text, errors, or timestamps, inspect those source results too.

Understand workbook date-system differences

Excel workbooks may use either the 1900 or 1904 date system. On Windows, the workbook setting is at File > Options > Advanced, in the section labeled When calculating this workbook; the option is Use 1904 date system. The setting is workbook-wide, so changing it can alter how existing date serials are interpreted. Do not toggle it as a casual fix for one incorrect cell.

The difference is especially relevant when copying or linking values between older workbooks or files created on different platforms. If a date shifts by roughly four years and one day, check whether the source and destination workbooks use different date systems. Compare the actual calendar date in the original file and review the workbook setting before deciding how to correct the values. Excel for Mac has its own settings layout, which can vary by release; check the workbook’s calculation settings rather than assuming a Windows menu path applies.

When to preserve text instead of converting

Not every date-shaped value should become an Excel date. An invoice number, date code, or identifier such as 20240131 may need to remain a fixed code, especially if leading zeros or exact digit sequences matter. Likewise, a source string can include a time zone or a timestamp format that should be preserved for audit or import purposes. Converting is useful when calculations, sorting by chronology, or date-based filters are required; retaining text can be safer when the value is an identifier or the source meaning is uncertain.

For reliable spreadsheets, keep the original source column when converting external data, document the expected order, and spot-check early, middle, and late rows. If dates will be used for analysis, store them as actual dates and apply a consistent display format. If data is passed to another system, confirm its expected date format before exporting because a displayed format and an underlying serial value are not the same thing.

Frequently Asked Questions

Why does Excel show a number instead of a date?
If the number is a date serial, select the cell and choose Home > Number Format > Short Date. If the value does not change, it may be text or a number that was never intended to be a date.

Why won’t Excel recognize dates like 31/12/2024?
The string may use day-month-year order while Excel is expecting another order. Convert it with Data > Text to Columns and choose DMY, or use a formula suited to the exact source layout.

Does changing the cell format convert text into a date?
No. Number formatting changes the appearance of numeric values, including real date serials. Text must first be converted or parsed before Excel can reliably use it in date calculations.

Why does a date show as ####?
Usually the column is too narrow, so widen it or AutoFit the column. If the value comes from a negative date or time calculation, check the formula and date order as well.

Why did dates change after copying between workbooks?
The workbooks may use different date systems, or the pasted values may have been interpreted under different regional conventions. Compare the original dates and check the 1900 or 1904 workbook setting before correcting anything.

How can I tell if a cell contains a real Excel date?
With the value in A2, enter =ISNUMBER(A2) in another cell. TRUE indicates a numeric value, which may be a date serial; confirm its displayed date and meaning rather than relying on the test alone.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

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

Spotted an error in this guide? Let us know.