Excel Serial Date to ISO Converter

Convert integer Excel dates in an explicit 1900 or 1904 system to Gregorian ISO dates; reject the fictitious 1900-system day 60 and fractional time values.

Description

Excel's 1900 and 1904 date systems assign different serials to the same Gregorian date.

Excel's 1900 and 1904 date systems assign different serials to the same Gregorian date. The extra fictitious day in the 1900 system requires an explicit correction when converting serials back to real dates.^{[1, 2]}

Convert integer Excel dates in an explicit 1900 or 1904 system to Gregorian ISO dates; reject the fictitious 1900-system day 60 and fractional time values.

When to use Excel Serial Date to ISO Converter

Use this tool to convert integer Excel dates in an explicit 1900 or 1904 system to Gregorian ISO dates; reject the fictitious 1900-system day 60 and fractional time values.

Choose the original CSV text, decoded cell strings, byte values or counts requested by this tool. Use the stated delimiter, encoding, indexing and number/date convention.

How to use Excel Serial Date to ISO Converter and understand its results

Choose the original CSV text, decoded cell strings, byte values or counts requested by this tool. Use the stated delimiter, encoding, indexing and number/date convention.
Enter serial, system using the field table and calculation rules, then run the tool.
Read isoDate. The explanation below defines the units, signs, ordering and any flags or placeholder values.
If the tool reports an input issue, correct the values and run it again. Apply the stated assumptions when interpreting a successfully returned result.

Input field	Meaning	Value type; see rules below	Requirement
serial	Serial	integer; minimum: 0; maximum: 9007199254740991	Required
system	System	string; one of 1900, 1904	Required

Enter an integer serial and choose 1900 or 1904. The 1900 range is 1 through 2,958,465; the 1904 range is 0 through 2,957,003, both ending at 9999-12-31. In the 1900 system, serial 60 is rejected because it represents nonexistent 1900-02-29; later serials lose the inserted day. The tool adds the corrected days to the selected UTC epoch and returns YYYY-MM-DD.

date = epoch + [serial − correction] whole UTC days; correction = 1 for 1900 serial > 60

Units, indexing, boundary conditions and exceptional cases are defined in the calculation rules above.

Output field	Meaning	Value type
isoDate	Iso date	string

json

{
  "input": {
    "serial": 40729,
    "system": "1900"
  },
  "output": {
    "isoDate": "2011-07-05"
  }
}

Limitations and assumptions

Fractions representing times, negative serials and dates outside the stated ranges are errors. The tool cannot infer which system an export used and does not format dates according to a locale or time zone.

Numeric inputs must be finite. Follow the calculation-specific rules for overflow, underflow and exceptional cases. Extremely large text inputs are rejected. Missing required fields, extra fields, wrong value types and violations of the calculation rules are input errors.

Alternative or Complementary methods

Use ISO Date to Excel Serial Export: Convert a valid Gregorian YYYY-MM-DD date to an integer Excel serial in an explicit 1900 or 1904 system, including Excel’s inserted 1900 leap-bug offset. For a complementary task, use Date Format Detection From Column Sample: List Gregorian ISO YYYY-MM-DD, MM/DD/YYYY, and DD/MM/YYYY formats compatible with every nonblank sampled cell; ambiguous day/month order is retained.

Further background: Date systems in Excel.

References

Excel Serial Date to ISO Converter — tool.io
Date systems in Excel — Microsoft

Similar or alternative tools

ISO Date to Excel Serial Export
Convert a valid Gregorian YYYY-MM-DD date to an integer Excel serial in an explicit 1900 or 1904 system, including Excel’s inserted 1900 leap-bug offset.
Date Format Detection From Column Sample
List Gregorian ISO YYYY-MM-DD, MM/DD/YYYY, and DD/MM/YYYY formats compatible with every nonblank sampled cell; ambiguous day/month order is retained.