Excel Date Cell Shows as Number? Fix the Serial Value

Errors & fixes · Updated November 19, 2026

If an Excel date cell shows a number, the value is usually a date serial displayed with the General or Number format. Select the cell and choose Home > Number Format > Short Date; if that does not change it into a date, the value may be text or may need conversion.

What the number in an Excel date cell means

Excel stores most dates as serial numbers: whole numbers that count days in a calendar system. The cell’s number format tells Excel how to display that value. For example, the serial value 45292 represents January 1, 2024, in the standard 1900 date system. With General formatting, Excel displays 45292; with a date format, it displays a calendar date.

This separation between a stored value and its appearance is central to understanding the problem. A date is not merely text that looks like a date. When Excel recognizes a value as a date, it can use that value in calculations, sort it chronologically, and apply date functions. Changing the display format usually changes only how the value looks, not the underlying serial number.

Excel’s standard Windows workbooks use the 1900 date system. It retains a historical compatibility quirk: Excel treats 1900 as if it had a February 29, although that date did not exist. As a result, serial 60 is the fictitious February 29, 1900, and serial 61 is March 1, 1900. This rarely affects ordinary current dates, but it matters when interpreting very early serial values or comparing data created in different spreadsheet programs.

Quickly change a serial number to a date

For a genuine Excel date value, change the number format rather than editing the number. In Excel for Microsoft 365 on Windows:

  1. Select the cell or range that displays the number.
  2. Open Home > Number Format in the Number group.
  3. Choose Short Date. Choose Long Date if you want a date written out with the weekday and month name.

If you need a specific layout, select the cells, press Ctrl+1, and choose Date on the Number tab. Pick a format from the list and confirm with OK. You can also choose Custom to specify a pattern such as yyyy-mm-dd, which displays a year-month-day order. The exact examples shown in the Date category can vary with Windows regional settings.

On a Mac, select the cells and use the Home tab’s number-format controls, or press Command+1 to open Format Cells. In Excel for the web, use the Home tab’s Number Format menu; the desktop File > Options settings described later are not available there in the same way. Short Date can look different across regions: one computer may show 1/2/2024 and another 02/01/2024. That is a display convention, not necessarily a different stored date.

Why changing the format sometimes does nothing

Formatting works only when Excel has a numeric date value to format. If a cell contains text such as an imported string, choosing Short Date may leave it unchanged. A quick clue is alignment: numbers are generally right-aligned by default and text is generally left-aligned, although manual alignment can override this clue. Another check is to select the cell and look at the formula bar. If the value begins with an apostrophe or remains a date-like string, it may be text.

Formulas can also return numbers that represent dates. For instance, subtracting one date from another returns the number of days between them, while a formula that adds days to a date can return a valid date serial. If the result is meant to be a date, format the result cell as a date. If it is meant to be a duration, keep it as a number instead. A format change does not fix an incorrect formula or turn arbitrary digits into a meaningful calendar date.

To check the cell format, select it and press Ctrl+1 on Windows. On the Number tab, look at the selected category. If it is General or Number, Excel will display the underlying serial as a number. Choose Date or an appropriate Custom format if the value is already a valid date.

Convert a date stored as text

Text dates often arrive from a website, a CSV file, a copied report, or a system that exports dates in a different regional order. Changing the number format alone will not convert them. One option is Data > Text to Columns: select the text-date column, start the wizard, choose the appropriate delimiter settings for the data, and on the final step select Date with the order that matches the source, such as MDY or DMY. Finish the wizard, then apply the desired date format.

A formula can convert text that Excel recognizes as a date. Suppose A2 contains the text 1/15/2024, interpreted as January 15 in a US regional setup. Enter this in B2:

=DATEVALUE(A2)

The result is the serial value 45306, which is January 15, 2024, in the 1900 date system. Format B2 as a date to display it as a calendar date. DATEVALUE uses the computer’s regional date interpretation, so ambiguous text such as 03/04/2024 might mean March 4 or April 3. Confirm the source order before converting a whole column; otherwise the result can be valid but wrong.

If the text uses an unusual format, has extra spaces, or combines dates with other characters, clean or separate the source data before conversion. For large imports, it is often better to set the date order during import than to repair the column afterward. After converting, compare a few known entries with the original source and check that the converted cells sort in chronological order.

When a date includes a time

Excel stores a time as the fractional part of a day. A value of 0.5 represents noon, and 0.25 represents 6:00 a.m. A date-time value therefore has a whole-number date portion plus a fraction for the time. If a cell displays only a date, the time may still be present but hidden by the format. Conversely, if a date-time cell is formatted as General, it may show a decimal number.

To display both parts, open Format Cells and choose a date-time format, or use a Custom format such as yyyy-mm-dd hh:mm. If a value should show only a date, a date-only format hides its time; it does not remove the time from the stored value. This distinction can affect comparisons: two cells that look like the same date might differ because one includes a time. When calculating elapsed time, decide whether you need full date-time precision or whole calendar days.

Check the workbook’s date system

Excel supports both the 1900 and 1904 date systems. The 1904 system numbers dates from January 1, 1904, while the 1900 system uses its own starting point and historical leap-year behavior. The same serial number can therefore represent dates 1,462 days apart in workbooks using different systems. When values are copied between workbooks, a date can appear shifted if the workbooks use different date systems.

On Windows, check File > Options > Advanced, then find the section labeled When calculating this workbook and look for Use 1904 date system. The option applies to the workbook, so do not switch it casually to make one cell look right: it can affect the interpretation of dates throughout the file. Excel for Mac also provides a 1904 date system setting, but its location can vary by release; check Excel’s calculation settings. Excel for the web does not provide the same desktop Options dialog.

If dates are consistently off by about four years after copying or importing, compare the source and destination workbooks’ date-system settings before changing individual cells. A format such as Short Date changes the appearance; it cannot correct a date-system mismatch. Preserve a copy before changing workbook-wide settings, particularly when formulas, historical records, or linked sheets depend on those dates.

Common causes and useful checks

When a date appears as a number, work through these checks in order rather than replacing the value manually:

  • The cell is set to General or Number: apply Short Date or a suitable date format.
  • The value is text: convert it with Text to Columns or DATEVALUE, taking care with regional date order.
  • The formula returns a serial: confirm whether the result should represent a date, a day count, or another number, then format it accordingly.
  • The cell contains a time fraction: use a date-time format if the time should be visible.
  • The date seems shifted: check the workbook date system and the source data’s date order.
  • The cell displays ####: widen the column first. Excel commonly uses this display when a formatted date or number does not fit; a negative date-time value can also be a cause.

A useful diagnostic is to select the cell and temporarily choose General. If the value changes to a plausible serial, it is likely a numeric date or date-time value. If it remains unchanged as text, formatting was not the missing step. Restore the date format afterward if needed. Do not type a different serial simply because the displayed number looks unfamiliar: doing so can silently change the actual date.

Prevent date-number problems when importing data

For files such as CSVs, inspect the date column during import instead of assuming every date-like string will be recognized correctly. Check whether the source uses month-day-year, day-month-year, or year-month-day order, and whether times or time zones are included. A value like 2024-01-15 is less ambiguous to a person than 01/02/2024, but import settings and regional conventions still matter.

After loading the data, format the date column, test sorting, and check a few records against the source. Keep a clear distinction between real Excel dates and text labels: dates should sort chronologically and support date calculations, while text may sort alphabetically. If a workbook is shared with users in different regions, a consistent display format such as yyyy-mm-dd can reduce confusion, though it does not change how the date is stored.

Frequently Asked Questions

Why does Excel show 45292 instead of a date? The cell likely contains a valid date serial but is formatted as General or Number. Select it and choose Home > Number Format > Short Date. Serial 45292 represents January 1, 2024, in the 1900 date system.

Will changing the cell format change the date? Usually not. Applying a date format changes how an existing numeric value is displayed, not the serial stored in the cell. If the value is text, or the workbook uses a different date system, formatting alone will not convert or correct it.

Why does Short Date leave the number unchanged? The value may be text rather than a numeric date. Convert it with Data > Text to Columns or, for text Excel recognizes, DATEVALUE. Confirm the source’s regional order before converting ambiguous dates.

Why does my date show as ####? First widen the column or use Home > Format > AutoFit Column Width. If that does not help, check whether the value is a negative date-time result or otherwise outside the range Excel can display with the chosen date system.

Why is the date off by several years in another workbook? The workbooks may use different date systems, or the imported text may have been interpreted in the wrong month-day order. Compare the 1900/1904 setting and verify several values against the original data before making workbook-wide changes.

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.