Date conversion can silently replace one valid date with another. Row counts and total amounts still reconcile while monthly reporting is wrong. This article covers Excel ingestion, principally XLSX and the cited openpyxl documentation. Legacy XLS, CSV and other reading engines require separate validation. Examples are constructed data and rule-based deductions, not production tests.
Classify the business field before converting a date
CSV import fidelity checks emphasize preserving source representations. Excel adds another layer: display, stored value, workbook date system and business meaning may differ. A screenshot showing '2026-11-03' does not establish whether the program reads text, a date object or a numeric serial, much less an instant in a specified time zone.
First classify each field: a business date, such as a contract's effective date; a local date and time, such as a factory handover; an absolute instant with a known source time zone; a duration, such as 36 hours of downtime; or an identifier that is not a date. Each target type has a different valid conversion path.
Formatting is a clue, not a business contract. A value of 1.5 representing days of elapsed time means 36 hours; the same value representing a measurement must not be converted into a date. Parsing the entire column as dates and choosing a type according to whether parsing fails conceals this semantic distinction.
For each source template, record the worksheet, field locations, permitted storage types, date-system detection, text formats, time-zone provenance, null policy and valid range. Revalidate after template changes. An unchanged column name such as 'time' does not preserve the old assumptions.
Determine the date system from workbook evidence
Microsoft documents Excel's 1900 and 1904 date systems. The serials for the same ordinary modern date differ by 1,462 days.[1] This changes numeric interpretation, not merely appearance. Applying a date display format cannot repair an incorrect baseline.
The documented example establishes conversion direction: 2011-07-05 is serial 40729 in the 1900 system and 39267 in the 1904 system.[1] Converting that day's 1904 serial into a 1900 serial adds 1462; the reverse subtracts it. The business date itself must stay unchanged.
Do not apply the adjustment to every numeric value. A reader may already have interpreted workbook metadata and returned the correct date object. Another adjustment introduces double conversion. When ingesting multiple files, do not apply the first file's system to all subsequent files. Read each workbook's metadata and then determine conversion needs by field.
openpyxl exposes the workbook baseline through wb.epoch.[2] Record it with source-file identity rather than guessing from the operating system, author or current defaults. If only bare serials remain after the original workbook has been lost, obtain a source declaration or authoritative reference dates. Unresolved interpretation belongs in a pending-verification state.
Handle legacy compatibility values, clock times and durations separately
The 1900 system includes a compatibility defect: Excel treats the nonexistent 1900-02-29 as a date. Microsoft explains its historical compatibility origin.[3] Consequently, the entire system cannot be modeled as a simple epoch plus elapsed days while assuming all early boundaries follow the real Gregorian calendar.
Reject or quarantine serial 60's fictitious date while its original value is still distinguishable, with an explicit reason. Do not silently map it to a neighboring real date. If a reader has already collapsed that serial into the same date object as another serial, checking the object's validity cannot recover the distinction. Preserve raw cell evidence before lossy conversion.
For modern business interfaces, define a suitable minimum date and quarantine early serials, negatives and values outside the business range. Historical archives need a separately specified textual or specialized date representation, rather than automatic rules designed for modern reporting. Range checks protect business semantics but cannot replace correct date-system handling.
A fractional numeric component can represent time within a day, but a numeric value can also represent duration. openpyxl supports timedelta for relevant duration formats, while Excel date/time objects carry no time-zone information.[2] Keep elapsed seconds, local clock values and business dates distinct instead of placing all three in a timestamp field.
Preserve evidence through a controlled conversion path
Receive a complete file, retain a controlled original and content digest, read its workbook date system and locate the contracted columns. Stage file identity, worksheet, cell address, original type and value, number format, reader version and interpreted result. If the reader converts dates early, validate how raw values are obtained; display strings alone cannot support boundary checks.
Branch by type. Use a date object already interpreted through a trusted path after checking its baseline and field contract; do not apply serial offsets again. Convert still-numeric date fields with their own workbook baseline. Accept textual dates only in declared formats. Do not let the machine's default locale decide whether 03/04/2026 means March 4 or April 3.
A business date should remain a calendar date, without an invented midnight and UTC conversion. Consider a constructed counterexample: 2026-11-03 is interpreted as midnight in Beijing and converted to UTC, becoming 16:00 on 2026-11-02. Downstream grouping by UTC date then assigns the business record to the previous day.
Convert a local date and time into an absolute instant only when its source zone is known and ambiguity resolved. Mark unknown zones as unknown. Convert durations into an agreed numeric unit and check whether negatives or spans beyond one day are allowed. Do not turn 36 hours into twelve o'clock and lose the full day, or invent a date to hide a type mismatch.
If cells contain formulas, separately establish whether the reader returns a formula, an existing calculated result or another representation, and whether that result matches current inputs. A formula's presence does not establish freshness; serial validation does not establish recalculation. Require confirmed source results or a validated calculation process when the calculation state cannot be established.
Test two workbooks and counterexamples, not just row counts
The first acceptance group uses two independent workbooks with different date systems and the same known modern date. Normalized business dates should match while source serials reflect their own baselines. Ingest the files together and verify that the first file's convention is not applied globally. Exercise the actual delivery reader and writer.
The second group includes integer serials, fractional serials containing time, explicitly formatted date text, ambiguous text, blanks and out-of-range values. Predefine acceptance, rejection or verification for each. Blanks must not become epoch dates. Test 59, 60 and 61 separately in the 1900 system, requiring that 60 is not silently merged into a real date.
The third group separates dates, instants and durations. Thirty-six hours must retain the whole duration; a midnight business date must not shift through zone conversion; an event with a declared zone must match an independent reference instant. Include database types, drivers and report display, rather than stopping at Python objects.
The fourth group round-trips normalized results through export and the destination tool. Compare semantic types and values at the agreed precision, not identical file bytes. Round-trip consistency is insufficient by itself because both directions may share an error. Also compare against independently known dates.
Reconcile dates record by record using business keys, and compare daily and monthly count distributions. Unchanged total rows and amounts will not reveal a whole batch shifted across months or years. If partial success is allowed, report accepted and quarantined counts alongside completeness so consumers know whether records are missing.
Repair the conversion rule instead of shifting the entire table
When dates appear four years apart, investigate by source file, date system, original type and reader version. Only some files may be misinterpreted, or a previous adjustment may already have occurred. Adding or subtracting 1462 across the production table would corrupt correct records. Identify the affected set and preview differences first.
Reprocess original files using an explicit new rule and processing version. Compare old and new dates, business groupings and exception counts, retaining the replacement relationship. Switch affected results only after reconciliation. On failure, keep the previous production version rather than exposing a half-converted batch to analytics.
The completion receipt should identify source files, date systems, rule and reader versions, acceptance and quarantine counts, validated types and unresolved items. Reliable date ingestion preserves the agreed business fact. A normal display, successful insertion and exception-free round trip each establish only part of that evidence.