Dates Not Showing as Dates in Excel? Fix the Format

Errors & fixes · Updated November 19, 2026

If dates are not showing as dates in Excel, first check whether the cells contain real date values or text: select a cell and look at the formula bar, then try Home > Number Format and choose Short Date. If the display does not change, the entry is probably text and must be converted; changing its format alone cannot turn text into a date.

Try the quick checks first

In Excel for Microsoft 365 on Windows, select one affected cell and inspect the Number Format box on the Home tab. Choose Short Date or Long Date. You can also open Format Cells with Ctrl+1, select the Number tab, and choose Date. On Mac, Command+1 opens Format Cells; menu names and ribbon layout may vary slightly. These steps change how a numeric value is displayed, not the value stored in the cell.

If the result remains unchanged, check whether the value is text. A green triangle in a cell may indicate that a number is stored as text, although the warning is not shown in every case. A formula test is more direct. With the value being checked in A2, enter this formula in another cell; it returns FALSE if A2 is text and TRUE if it is a numeric Excel date or another number: =ISNUMBER(A2).

Also check the column width. A string of number signs, such as #####, usually means the column is too narrow to show a numeric date or time. Widen the column by dragging its right boundary, or double-click that boundary to AutoFit. If the value still looks wrong after widening, investigate the underlying value and date format rather than assuming the date is missing.

How Excel stores dates

Excel does not store an ordinary date as a special text label. It stores dates as serial numbers and displays those numbers using a date format. In the standard 1900 date system, January 1, 1900 is serial 1, and January 1, 2024 is serial 45292. A date-time value includes a fractional part for the time of day: noon is one-half of a day after midnight.

This is why changing a cell from General or Number to Date can make a value suddenly look like a calendar date. It is also why formatting a text string as a date often appears to do nothing: the cell still contains characters, not a date serial. Formatting controls appearance; conversion changes the underlying data type.

The date system matters when workbooks move between files or software. Excel supports 1900 and 1904 date systems, which assign different serial numbers to the same modern date. Excel usually handles a workbook’s chosen system internally, but linking or copying raw date serials between workbooks using different systems can shift displayed dates by about four years. Check the workbook’s date-system setting if many dates are consistently offset, rather than correcting every cell by hand.

Tell a real date from text

A cell may look like a date even when Excel treats it as text. Common clues include left alignment under the usual General format, an apostrophe before the entry in the formula bar, or a date format change that has no effect. Alignment is only a clue: users can apply custom alignment, so it is not conclusive by itself. The ISNUMBER test is useful when you need to check several suspect cells.

Text can arise from a leading apostrophe, spaces before or after the date, nonbreaking spaces copied from a website, or an import that treated a column as text. A value such as 2025-01-15 may also be read differently depending on the source and regional settings. A date-looking string is not guaranteed to be a date value just because people can recognize it.

For a quick visual check, temporarily apply General to a cell you believe contains a date. A real date will usually show its serial number, while text will keep its characters. Restore the date format afterward. Do not use this check as a way to overwrite data: changing the format is reversible, but editing or replacing values can lose information.

Convert text dates into Excel dates

For a small number of entries, re-entering the date in a format Excel recognizes under your regional settings can be simplest. For a column, use Data > Text to Columns on Windows. Select the text-date column, choose Delimited, click Next twice without selecting a delimiter, choose Date in the column data format, select the order that matches the source (such as MDY or DMY), and click Finish. Make a copy of the column first if the data is important, and verify several results afterward.

For example, 04/05/2025 means April 5 in month-day-year order but May 4 in day-month-year order. Choosing the wrong order can produce valid-looking but incorrect dates. The Text to Columns date option is particularly helpful when every entry follows one known order. If the source contains mixed formats, inspect and separate those cases instead of forcing one interpretation across the entire column.

A formula can convert a text date when Excel can interpret its content. With the text value 1/15/2025 in A2 under a US month-day-year locale, enter this in B2: =DATEVALUE(TRIM(A2)). The result is the date serial 45672 in the 1900 date system; format B2 as a date to display January 15, 2025. Fill the formula down for other rows, check the results, then copy the converted cells and use Paste Special > Values if you need fixed values rather than formulas.

DATEVALUE depends on the text pattern and locale. It may fail or misread ambiguous strings, and it is not a universal parser for every date format. TRIM removes ordinary leading and trailing spaces, but not every invisible character copied from a web page. If a formula returns #VALUE!, inspect the exact source characters and regional order; use Text to Columns with a deliberate date order or clean the imported text before converting it.

Choose a display format that fits

Once the cells contain real date values, select a format that is clear for the people who will read the sheet. Short Date is compact, while Long Date spells out the day and month. For a consistent custom display, open Format Cells, choose Custom, and use a pattern such as yyyy-mm-dd. This year-month-day format reduces ambiguity when dates are read across regions, although it does not change the stored date.

Be careful with formats such as 3/4/25: readers may interpret that as March 4 or April 3. A four-digit year avoids uncertainty about the century, and spelling the month can make reports easier to scan. If you need the date to remain sortable as a date, keep it as a numeric date value and use formatting; converting it to a text label with a formula or an apostrophe can interfere with date calculations and chronological sorting.

Imports, regional settings, and CSV files

Dates often break during import because a file’s date order differs from the computer’s regional settings. A CSV file contains plain text and does not reliably preserve Excel number formats or data types. Opening a CSV directly may cause Excel to guess how to interpret a column. For more control in Windows Excel, use Data > From Text/CSV, review the preview, and check the detected column type before loading the data. If the import is ambiguous, transform the column deliberately rather than trusting an automatic guess.

In Power Query, a date column can be assigned a type using the source’s locale. This is useful for a file with day-month-year dates opened on a computer set to month-day-year. Choose the appropriate locale when converting the column, then confirm that dates such as 04/05/2025 have the intended meaning. Excel for Mac and Excel for the web may present different import options or ribbon layouts, so the exact controls can differ; the core issue remains that the source date order must be interpreted correctly.

For recurring imports, keep the original text column until the converted dates have been checked. Compare a few known dates at the start, middle, and end of the data, and look for blanks or errors. This makes it easier to spot a file whose format changed between deliveries. If other spreadsheet users will open the file, consider using unambiguous ISO-style source dates such as 2025-01-15 where the export system supports them, then confirm Excel has imported them as dates rather than text.

Why dates show ####, strange numbers, or the wrong day

  • ####: Widen the column first. If it persists, check for a negative date or time calculation; Excel’s standard date system cannot display negative date-time values as ordinary dates.
  • A large number: The cell may contain a valid date serial but use General or Number format. Apply a date format to display it as a calendar date.
  • The same text after formatting: The entry may be text. Convert it using Text to Columns, a suitable formula, or a corrected import.
  • A date one month or day off: Check whether Excel interpreted the source as MDY or DMY. Reconvert from the original text with the correct order if needed.
  • A time appears with the date: The underlying value may include a fractional time. Choose a date-only format if the time should be hidden, or retain a date-and-time format if it matters.

Do not delete the time or replace a suspicious date just to make the display look right. A format can hide the time while retaining it in the value, which may affect calculations. Likewise, a number that displays as the wrong date may be a valid serial interpreted under the wrong date system or regional order. Identify the cause before changing data.

Frequently Asked Questions

Why does changing the cell format not fix my date?
If the entry is stored as text, a date format only changes the display rules and does not convert the characters into a date serial. Convert the value, then apply the desired format.

How do I stop Excel from changing dates automatically?
When importing, use Data > From Text/CSV and inspect the column type rather than opening a CSV and relying on automatic guesses. For data that must remain text, import or set the column as Text before loading or entering the values.

Why does Excel show a number instead of a date?
A date is stored as a number. Select the cell and apply Short Date, Long Date, or a suitable custom date format. If the number does not represent the expected date, check the source value and workbook date system.

Why is Excel reading 04/05/2025 incorrectly?
The string is ambiguous: it can mean April 5 or May 4. Convert it using the source’s known order, such as MDY or DMY, and verify a few entries against the original data.

Can I use a date that is stored as text in formulas?
Some formulas can interpret text dates, but results depend on the date pattern and regional settings. Converting the column to genuine date values first is generally more dependable for sorting, filtering, and date arithmetic.

Related

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.