Excel date serials and the 1900 and 1904 epochs: spreadsheet and API date handling

What to take away

What the number means

Astronomers have counted days as consecutive integers for centuries. The Julian day number exists because subtracting two integers is easier to get right than subtracting two calendars. A sheet does the same thing from a different starting point.

Subtracting two dates is exact and carries no zone. A difference computed this way cannot be moved by a clock change, because the numbers never knew about one.

The two epochs

Excel's default date system counts from the start of 1900. A second system, inherited from early Macintosh versions, counts from 1904. The offset is 1,462 days. Excel's toggle sits at File > Options > Advanced, in the workbook calculation settings.

A workbook on one system and a workbook on the other show different dates for the same stored number. Copy values between them without converting and everything shifts by just over four years. Copying cells usually carries the format and the conversion; pasting raw numbers does not.

The 1900 system keeps a 29 February that never existed, so serials below March 1900 run one day out against a true count. That is the best known case of the general leap year problem, and it is a compatibility decision nobody intends to fix.

Treat serials as interchangeable only from March 1900 onward. Test the boundary rather than assuming another tool reproduces the same defect. LibreOffice Calc defaults to an 1899-12-30 epoch and offers 1900 or 1904 under Tools > Options > Calc > Calculate.

The spreadsheet and API tools, reviewed

Prices below are list prices as of 2026. Per-seat plans move with region, billing term and currency, so treat them as typical.

Excel and Google Sheets. Both store a date as a serial. Excel offers the 1900 and 1904 systems; Sheets uses 1899-12-30 and offers no toggle.

Workday arithmetic runs through WORKDAY.INTL and NETWORKDAYS.INTL in both, which accept a weekend code and a holiday range. Sheets also has WORKDAY and NETWORKDAYS without the suffix. The INTL variants are the ones to use when a closure table matters.

Power Query. Reads a column as a type you choose. Date.From and DateTime.From convert text under the file's locale setting, so the same eight characters can land on two different dates in two files. Set the locale explicitly on import rather than trusting the machine.

pandas. read_csv with parse_dates returns datetime64, a count from 1970-01-01 in nanoseconds. It has no 1900 epoch and no 1904 epoch. A serial pasted from Excel arrives as a number near 45,000 and needs pd.to_datetime(serial, unit='D', origin='1899-12-30') to become a date.

openpyxl and SheetJS. openpyxl reads and writes xlsx in Python, and its from_excel function converts a serial once you know the workbook's epoch. SheetJS is the JavaScript and Node reader behind many browser upload tools. Set cellDates: true and it hands back date values instead of raw numbers.

Nager.Date and the Python holidays package. Both publish country and subdivision holiday tables you can feed into a WORKDAY.INTL holiday range. Both carry a version date. Neither is authoritative for a filing deadline; the CRA, the IRS or the state board is.

Days-between-dates APIs. Most take two ISO dates and return an integer, and most count the difference rather than the span. A spreadsheet counting function includes both endpoints. That is a difference of one, and it survives every test built on long spans. The conventions are set out under counting between dates.

Text dates

A text date displays exactly like a date cell. It differs in three ways. It sorts alphabetically, so the 10th lands before the 9th. It is invisible to date functions, which error or treat it as zero. And it converts on import by locale.

Import in ISO 8601 order, largest component first. It parses unambiguously under every locale and sorts correctly while it is still text. Then test the column rather than the eye: a check that every populated cell is numeric finds text dates no amount of looking will.

The failure surfaces late. A closure range with one text date in it does not break until a count reaches that part of the range, which can be months after the import.

What travels, and what does not

Send text, not the serial. The serial is a number whose meaning depends on the workbook's epoch, and nothing on the wire carries the epoch. 2027-03-05 needs no agreement about epochs at all.

Send a date and a moment as different things. A date has no zone. A moment has one and needs an offset. A sheet cannot tell them apart because both are numbers, so the distinction has to be made at the boundary, and that boundary contract is set out under spreadsheet and interface tools.

Precision

The time fraction is binary floating point, so a time written as a decimal rarely lands exactly on the second you meant. Formatting hides it. An equality test against a window boundary does not.

Compare with clamping rather than equality. Take the median of a value and the two window bounds instead of asking whether the value equals a bound, and the question never arises. The window arithmetic is under working hours, and the day-level counting it supports is under counting a month.

Common questions

Why does my date show as a five-figure number?

The cell is formatted as a number and the value is a serial. Format it as a date. The value is correct.

Can I store a date as text on purpose?

Yes, in ISO order, when the sheet is a transport rather than a calculator. Convert it to a serial before any date function touches it.

How do I tell which date system a workbook uses?

Enter a known date and read the serial, or check the workbook's settings. The known-date test is quicker and works where the setting is not obvious.

Which of these tools honors the 1904 epoch?

Excel and LibreOffice Calc switch epochs in their settings. Sheets and Google Apps Script stay on 1899-12-30, and pandas counts from 1970-01-01. Convert a 1904 serial before handing it to any of them.