Fix "OverflowError: date value out of range" When Reading Excel

Excel stores a date as a number — the count of days since 30 December 1899 — and marks the cell as a date only through its number format. Python libraries convert any number in a date-formatted cell into a datetime. That works until a cell holds a number that cannot be a date: Python's datetime stops at 31 December 9999, Excel's calendar at serial 2,958,465, and a number like 99,999,999 is far beyond both. Depending on the library and version, the result is OverflowError: date value out of range, a warning that the cell "will be treated as an error", a silent NaT in pandas, or an OutOfBoundsDatetime later in the pipeline. This page shows where each one comes from, how to see the original numbers, and how to convert them correctly. It belongs to Troubleshooting Common Python Excel Errors.

Prerequisites

pip install pandas openpyxl python-calamine

contracts.xlsx has an Expiry column formatted as dd mmm yyyy. Two rows are ordinary dates. The others are the kinds of values that turn up in real exports: 2958465 (31 December 9999, used as "never expires"), 99999999 (another "never" convention), 20261231 (a yyyymmdd date typed as a number, then formatted as a date), and -5 (a negative number in a date column).

Step 1 — See the symptom your stack produces

import warnings
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.datetime import from_excel

with warnings.catch_warnings(record=True) as caught:
    warnings.simplefilter("always")
    ws = load_workbook("contracts.xlsx")["Contracts"]
    print([c.value for c in ws["B"]][1:])
print(caught[0].message if caught else "no warning")

try:
    from_excel(99999999)
except OverflowError as exc:
    print("from_excel:", type(exc).__name__, exc)

print(pd.read_excel("contracts.xlsx")["Expiry"].tolist())

Three behaviours from the same file:

• openpyxl 3.1 converts the in-range serials and replaces the impossible ones with the string '#VALUE!', emitting UserWarning: Cell B4 is marked as a date but the serial value 99999999 is outside the limits for dates. The cell will be treated as an error. Older openpyxl releases raised OverflowError: date value out of range at this point and stopped reading altogether.
• openpyxl.utils.datetime.from_excel, which scripts call to convert serials themselves, still raises OverflowError: date value out of range for the same number — as does any hand-written datetime(1899, 12, 30) + timedelta(days=n).
• pandas with the openpyxl engine turns the error cells into NaT. The read succeeds, the warning scrolls past, and contract C-103's expiry has quietly disappeared.

The -5 is converted to 25 December 1899 — a valid datetime, but certainly not a contract expiry — and 20261231 is far beyond 9999 and also becomes NaT.

Step 2 — Get the raw numbers back

To decide what a bad value means, you need the number that was stored, which openpyxl has already discarded. The calamine engine keeps it: for a date-formatted cell outside the date range, it returns the number itself:

raw = pd.read_excel("contracts.xlsx", engine="calamine")
print(raw.assign(kind=raw["Expiry"].map(lambda v: type(v).__name__)).to_string(index=False))

Out-of-range cells come back as plain integers — 99999999 and 20261231 — next to the converted dates, and the -5 appears as a time value, because calamine interprets a small negative serial as a time of day. A column mixing types like this is exactly what you want while diagnosing: every impossible value is visible. If calamine is not available, read the cell values straight from the sheet XML as shown in Inspect the XML Inside an .xlsx File with Python.

Step 3 — Decide what each value means, then convert

The fix is a mapping from raw serial to intended date, written down rather than guessed:

import datetime as dt

EXCEL_EPOCH = dt.datetime(1899, 12, 30)
NEVER = pd.Timestamp("2262-04-11")      # stand-in for "no expiry" inside pandas' safe range

def to_expiry(value):
    if isinstance(value, dt.datetime):
        return NEVER if value.year == 9999 else pd.Timestamp(value)
    if isinstance(value, (int, float)):
        n = int(value)
        if n in (2958465, 99999999):
            return NEVER
        if 19000101 <= n <= 21001231:                       # yyyymmdd typed as a number
            return pd.to_datetime(str(n), format="%Y%m%d")
        if 1 <= n <= 2958465:
            return pd.Timestamp(EXCEL_EPOCH + dt.timedelta(days=n))
    return pd.NaT                                          # negative serials, times, text

raw["Expiry clean"] = raw["Expiry"].map(to_expiry)
raw["Never expires"] = raw["Expiry clean"] == NEVER
print(raw[["Contract", "Expiry clean", "Never expires"]].to_string(index=False))

The 9999 date and the 99,999,999 sentinel both become "never expires", flagged in their own column; 20261231 becomes 31 December 2026; real serials convert normally; the negative value becomes NaT and should be reported back to whoever owns the sheet. Choosing a stand-in for "never" inside pandas' range avoids the next error in this family.

Step 4 — Avoid OutOfBoundsDatetime downstream

pandas 3 stores datetimes at the resolution that fits, so a column containing 31 December 9999 reads fine as datetime64[us]. The trouble starts when code or a library forces nanosecond precision, whose range ends on 11 April 2262:

dates = pd.read_excel("contracts.xlsx", nrows=2)["Expiry"]
print(dates.dtype)
try:
    dates.astype("datetime64[ns]")
except Exception as exc:
    print(type(exc).__name__, exc)

Four different limits are in play, and each error in this family comes from crossing one of them:

Excel itself shows ######## in a date-formatted cell whose value is negative or above 2,958,465; Python's datetime cannot go past 9999 either, which is the OverflowError; and the nanosecond limit in 2262 is a pandas storage choice rather than a calendar limit, which is why it surfaces later and only in some code paths. OutOfBoundsDatetime: Out of bounds nanosecond timestamp: 9999-12-31 00:00:00 appears whenever a 9999 date meets nanosecond storage — older pandas versions, astype("datetime64[ns]"), or libraries that assume it. Mapping sentinel dates to a flag plus a safe stand-in, as in Step 3, removes the problem at the source. The general rules for date columns are in Parse Excel Dates into Python Datetimes with pandas.

Step 5 — Fix the workbook if you own it

If the file is produced by your own process, stop the bad values at the source. A column of yyyymmdd numbers or IDs should not carry a date format; set it back to General with openpyxl, and write "no expiry" as an empty cell plus a status column rather than a magic date:

wb = load_workbook("contracts.xlsx")
ws = wb["Contracts"]
fixed = 0
for (cell,) in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    if cell.value == "#VALUE!" or (hasattr(cell.value, "year") and cell.value.year < 1900):
        cell.number_format = "General"
        fixed += 1
wb.save("contracts_checked.xlsx")
print("cells reset to General:", fixed)

The cells openpyxl could not convert lost their original numbers on load, so this pass only marks them for attention — the real fix is in the job that writes the file. Detecting impossible values in incoming files belongs in validation, as in Validate Excel Columns Before Import with pandas.

Common pitfalls

• Ignoring the warning. openpyxl 3.1 keeps reading and pandas shows NaT; turn warnings into errors in tests with warnings.simplefilter("error").
• Converting serials by hand. timedelta(days=n) raises OverflowError for large n; range-check first.
• Sentinel dates in pandas. 9999-12-31 works in pandas 3 but fails wherever nanosecond precision is forced.
• The 1900 leap-year quirk. Excel counts a non-existent 29 February 1900, which is why the epoch used for modern serials is 30 December 1899, not 1 January 1900.
• 1904-system workbooks. Some Mac workbooks count from 1904; see Handle the 1904 Date System in Excel Files.

Conclusion

The error means a date-formatted cell holds a number that cannot be a date. openpyxl 3.1 turns such cells into #VALUE! with a warning and pandas into NaT; from_excel, hand-written conversions and older openpyxl raise OverflowError; 9999 dates later trigger OutOfBoundsDatetime under nanosecond precision. Read the raw serials with the calamine engine, map sentinels, yyyymmdd numbers and real serials explicitly, report the rest, and fix the number format in the workbook that produced them.

Frequently asked questions

What causes "OverflowError: date value out of range" when reading Excel? A cell with a date number format contains a number outside the range Python can turn into a date, such as 99999999 or a yyyymmdd value like 20261231. Converting it with from_excel, older openpyxl versions, or datetime arithmetic raises OverflowError.

Why does openpyxl say a cell is marked as a date but outside the limits for dates? openpyxl 3.1 found a date-formatted cell whose serial value is beyond Excel's calendar. Instead of raising, it warns and returns the string #VALUE!, which pandas reads as NaT.

How do I read the original number from an out-of-range date cell? Read the file with pd.read_excel(path, engine="calamine"). For date-formatted cells outside the valid range, calamine returns the stored number instead of a date.

How should I store "no expiry" dates from Excel in pandas? Map sentinel values such as 9999-12-31 or 99999999 to a separate boolean flag, and either leave the date empty or use a stand-in inside pandas' safe range, so nanosecond-precision code never meets year 9999.

Related

• Up one level: Troubleshooting Common Python Excel Errors — fixes for the errors Excel automation hits most.
• Fix Excel Serial Numbers Showing Instead of Dates — the opposite problem, dates without a date format.
• Convert Text Dates Like 20240131 with pandas — yyyymmdd values as text.
• Speed Up pandas Excel Reads with the Calamine Engine — the engine used to recover the raw values.