Excel Serial Date Converter — Serial Number to Date & Batch Mode

How to use the Excel Serial Date Converter

Windows 1900 versus Mac 1904 date system

Excel uses two different date system epochs depending on the platform and workbook settings. Furthermore, using the wrong epoch when converting serial numbers produces dates that are 1,462 days off — almost exactly 4 years.

The 1900 leap year bug

Excel's 1900 date system includes a deliberate bug: it treats 29 February 1900 as a valid date — even though 1900 was not a Gregorian leap year. Furthermore, Lotus 1-2-3 introduced this bug originally and Excel maintained it for compatibility. Moreover, this means Excel serial number 60 corresponds to the non-existent date 29 February 1900 in the 1900 system. For practical purposes, this only affects dates before 1 March 1900 — irrelevant for most modern uses.

How Excel serial dates are calculated

An Excel serial date number counts the days elapsed since the epoch date. Furthermore, adding the serial number (as a count of days) to the epoch date gives the Gregorian date.

Detecting which date system a workbook uses

In Excel, go to File → Options → Advanced and scroll to "When calculating this workbook". Furthermore, if "Use 1904 date system" is checked, the workbook uses Mac dates. Google Sheets always uses the 1900 system. Moreover, when copying data between Excel workbooks with different date systems, Excel adjusts dates automatically — but exported CSV or text files carry the raw serial number without system information.

Worked example: debugging a date display issue

A data analyst exports a date column from a database as integer values: 45658, 45721, 45812. They need to know what dates these represent.

What is an Excel serial date number?

An Excel serial date number is how Microsoft Excel stores date values internally. Furthermore, every date in Excel is stored as a positive integer representing the number of days since the epoch date. The human-readable date display (e.g. "01/01/2025") is simply a formatting layer over the underlying serial number.

Serial numbers appear as raw integers when date cells lose their formatting — during CSV export, database import, API transfer or copy-paste into plain text. Furthermore, any data pipeline that extracts dates from Excel without preserving cell formatting produces raw serial numbers. Moreover, these numbers are meaningless to humans without conversion.

Where serial dates appear in data work

CSV exports from Excel often convert date cells to their serial number when the receiving system does not recognise the date format. Furthermore, SQL databases importing from Excel may store dates as integers if the date column type is not specified. Moreover, Python's openpyxl library reads Excel dates as serial floats unless explicitly converted. Data engineers encounter serial dates in ETL pipelines, legacy system migrations and cross-platform data exchanges.

Serial numbers with decimal fractions — time components

Excel serial numbers can include a decimal fraction representing the time of day. Furthermore, 0.5 represents noon (12:00:00), 0.25 represents 06:00 and 0.75 represents 18:00. A serial value of 45658.5 means 1 January 2025 at 12:00:00 noon. Moreover, this tool handles integer serial numbers — for serial dates with time components, truncate the decimal before converting to get the calendar date.

Why Excel serial date conversion matters

Data quality depends on correct date representation. A serial number stored in a date column that loses its format becomes an unreadable integer in any downstream system. Furthermore, batch converting hundreds of serial numbers from a legacy data export is a recurring task in data cleaning and ETL work. Moreover, the batch mode reduces a multi-hour manual task to under one minute for files with up to 500 dates.

Cross-platform workbook sharing creates serial date confusion. A Mac Excel workbook sent to a Windows user may show dates shifted by 4 years. Furthermore, the 1900/1904 system difference is invisible to the end user — the dates look plausible but are wrong. Moreover, identifying this error requires checking the workbook's date system setting and then converting all dates with the correct epoch.

How Python and programming languages handle Excel dates

Python's openpyxl reads Excel date cells as datetime objects automatically in most cases. Furthermore, when reading xlrd (for older .xls files), dates may come through as serial floats. The conversion formula in Python is: datetime.date(1899, 12, 30) + timedelta(days=serial). Moreover, pandas' read_excel uses openpyxl by default and converts dates automatically — but raw CSV exports from Excel still produce serial integers that require manual conversion.

Frequently asked questions

Related Date & Time tools

Every tool on LazyTools runs in your browser. Nothing is uploaded or stored.