Excel Shows Date Serial Numbers: Format Them and Check the Date System

Confirm what the number represents

Look for a source specification, a workbook setting, or a known record whose calendar date you can independently verify. Similar-looking numbers might be order IDs, elapsed days, Unix timestamps, or spreadsheet serials. The number of digits is a clue, not proof.

For the sample, the exporter explicitly states that serial contains Excel dates using the 1900 system. Keep that statement with the imported data. CSV does not carry the source workbook's date-system setting, so a bare serial column needs accompanying documentation to remain interpretable.

Display the sample without changing its values

In Excel, select the numeric serial cells B2:B4. Open Format Cells and choose Custom, then enter yyyy-mm-dd hh:mm. The result should match the expected output. If the cells remain unchanged, check =ISNUMBER(B2): imported text numbers require a separate numeric conversion before the date format can affect their display.

Avoid overwriting the original serial column during the first pass. A helper column containing =B2 lets you apply a different display while retaining the source value beside it. Compare a known date and the time before accepting the transformation.

C2: =B2             Format as yyyy-mm-dd hh:mm
D2: =ISNUMBER(B2)   Expected: TRUE for a numeric serial
E2: =MOD(B2,1)      Expected for D1: 0
E3: =MOD(B3,1)      Expected for D2: 0.5

Related help: Distinguish numeric dates from dates stored as text

Check a four-year shift before adjusting anything

Excel supports the 1900 and 1904 date systems. For 1 January 2026, the serials are 46023 and 44561 respectively, a difference of 1462. In Windows desktop Excel, inspect File > Options > Advanced > When calculating this workbook > Use 1904 date system. Mac versions expose the setting under calculation preferences.

Check the source and destination separately. When raw 1904 serials must be used in a 1900 workbook, adding 1462 produces the equivalent modern dates. The reverse conversion subtracts 1462. Do this only when a known date confirms that raw serials were imported without conversion; a copy operation may already have adjusted them.

Retain fractions and record the conversion direction

A serial's fractional part represents a fraction of a day: .5 is 12 hours and .25 is 6 hours. Rounding the number to an integer discards or shifts the time. If your output should contain dates only, decide deliberately whether to remove the time and retain the original value for audit.

Do not toggle the workbook's date system merely to make one imported column look right. That changes the context in which other date numbers are interpreted. Prefer a documented helper-column adjustment for the affected raw data, then verify against source records before replacing anything.

Handle early dates and unknown origins explicitly

Excel's 1900 system includes a historical compatibility error: it treats 1900 as a leap year. Serial 60 represents the nonexistent 29 February 1900. A general-purpose Gregorian date library cannot represent that as a valid calendar date. Reject or label that sentinel instead of silently converting it to a neighboring day.

For dates around January and February 1900, use conversion logic that explicitly handles Excel's convention. A single modern-date base-offset shortcut is not a universal converter. Serial dates also contain no time-zone identity; noon does not establish whether a record means local time or UTC. The checker does not read workbook settings or convert serial numbers for you.