AI Spreadsheet Dates: Check the Workbook’s Date System

Imagine an independent publisher importing an old workshop schedule with an AI-generated script. A date meant to be 27 October 2026 appears as 26 October 2022. The output is neatly formatted and sorts correctly, but the calendar meaning has changed. In this fictional case, the importer received a date number and interpreted it using the wrong workbook date system.

This is a useful check when asking AI to clean spreadsheet data. Establish what the stored values mean before choosing a prettier display format. A plausible-looking date can still point to the wrong day.

Identify the date system behind the number

Microsoft’s date-system guide explains that Excel supports the 1900 and 1904 systems. They use different starting points, so the same calendar date has different serial numbers. The difference is 1,462 days: the 1900-system number is larger by that amount.

For the publisher’s import, record the source workbook’s actual setting. Do not infer it from the file’s age or the colleague’s computer. The question is which system this workbook uses and how the import process interprets its values.

Microsoft places the Windows setting under File > Options > Advanced, in “When calculating this workbook”, as “Use 1904 date system”. Its Mac instructions locate the setting under Excel > Preferences > Calculation. Inspect the relevant workbook; changing the setting is a separate decision.

Follow one value through the conversion

For the worked example, the numerical value 44860 has two interpretations:

• In the 1904 system, it represents 27 October 2026.
• In the 1900 system, that same number represents 26 October 2022.
• The 1900-system serial for the intended 27 October 2026 is 46322.

These conversions were checked with openpyxl 3.1.5 for this article. The difference between the two correct serials is 46322 − 44860 = 1462. The problem in the fictional import is the interpretation attached to a value, rather than a random change to the day or month.

A repeated 1,462-day discrepancy is a reason to investigate this particular mismatch. Compare it with the source setting and known dates before choosing a correction. Do not add the offset to every numerical field merely because some rows look too early.

Separate formatting from meaning

Microsoft’s number-format guidance explains that formatting changes appearance without changing the value used in calculations. Date formats display serial numbers as dates. On a working copy of a numerical date cell, a General or Number format can help expose the underlying number for inspection.

Keep the original display available alongside that number. For the publisher, a useful review record includes the source filename, cell address, visible date, numeric value and workbook date system. Those observations let the reviewer explain how the destination date was obtained.

Choosing a different date display will not repair this example’s mistaken interpretation. A clear label such as 2022-10-26 remains the wrong outcome when the intended date is 2026-10-27. Agree the calendar date first, then select its output representation.

Give the importer the workbook context

The openpyxl date documentation explains that XLSX dates can be stored as ISO 8601 strings or as numbers. For numerical dates, the workbook’s date system determines their interpretation. The library exposes that setting through wb.epoch, giving a Python importer a way to inspect the source instead of assuming its starting point.

If AI is helping write an import, ask it to explain how it reads the workbook’s date system, which fields it treats as dates and what it returns. Require the proposed transformation to preserve the source’s intended calendar dates. Keep a small set of expected outcomes ready to compare with the result.

Check whether the reading software has already returned a calendar-date value. That is a different input from an uninterpreted serial number. Applying an additional offset after a date-aware conversion would change the date again. The transformation needs a defined input representation, not just a column name.

Keep bare-number handovers explicit

The informational RFC 4180, published in October 2005, describes CSV records and fields. A field containing only 44860 does not, by itself, declare which Excel date system gives that number meaning. My practical recommendation is to carry that interpretation explicitly when passing numerical dates between systems.

The publisher could supply the workbook with its setting intact, or provide a separately checked export with an agreed date representation and import rule. If raw serials are necessary, include the date system in the handover notes. Preserve enough information for the recipient to explain the conversion.

Keep text dates, blanks and numerical dates identifiable during review. Ask how each input type will be handled rather than letting one conversion rule silently stand in for all of them. This helps make the scope of the proposed cleanup inspectable.

Check more than one calendar date

Two further verified examples help widen the check:

• 29 February 2024: serial 45351 in the 1900 system and 43889 in the 1904 system.
• 15 January 2027: serial 46402 in the 1900 system and 44940 in the 1904 system.

For all three example dates, conversion to a serial and back through the same system returned the original date. That gave six successful round trips, and all three serial pairs differed by 1,462. These are numerical checks, not a test of a real publisher’s workbook or an Excel copying workflow.

For your own import, add representative known dates and record the expected result before running the transformation. Include an already interpreted date as a control against applying the correction twice. Check the agreed handling of blanks and text separately; those additional checks are recommendations, not reported test results here.

Keep the checked rule with the output

Before using the converted schedule, compare selected destination dates with the source and a reliable project record. Retain the original workbook, the transformation rule and the reviewed output as identifiable versions. If only part of a mixed-source file needed conversion, record that boundary.

If the imported dates feed calculations, review those dependencies too. MaryChuks’ guide to testing AI-written Excel formulas across a filled range covers a separate check: whether the intended references survive copying.

A useful date-cleanup handover explains the source representation, its date system, the transformation applied and the examples that passed. That gives another person a way to verify the calendar meaning before relying on the polished spreadsheet.

Sources

• Microsoft: Date systems in Excel.
• Microsoft: Available number formats in Excel.
• openpyxl: Dates and Times.
• RFC 4180: Common Format and MIME Type for CSV Files — October 2005, informational.

Guidance checked on 27 October 2026. Microsoft and openpyxl pages do not display publication dates. The publisher and schedule are fictional; numerical checks used openpyxl 3.1.5.