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.

Description

Excel stores dates as day serials under either a 1900 or 1904 system.

Excel stores dates as day serials under either a 1900 or 1904 system. The 1900 system includes a fictitious leap day, so choosing the correct system matters when converting real Gregorian dates.^{[1, 2]}

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.

When to use ISO Date to Excel Serial Export

Use this tool to 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.

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 ISO Date to Excel Serial Export 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 isoDate, system using the field table and calculation rules, then run the tool.
Read serial. 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
isoDate	Iso date	string; maximum length: 10	Required
system	System	string; one of 1900, 1904	Required

Enter a real Gregorian date spelled YYYY-MM-DD and select the system. The parser validates the year, month and day, then computes the UTC day difference from the selected epoch. The 1900 system accepts dates from 1900-01-01 and inserts the extra serial after February; the 1904 system starts at serial 0 on 1904-01-01. Earlier dates are errors. Only whole-date serials are returned.

1900 serial = days since 1899-12-31 + 1 for dates ≥ 1900-03-01; 1904 serial = days since 1904-01-01

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

Output field	Meaning	Value type
serial	Serial	integer; minimum: 0; maximum: 9007199254740991

json

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

Limitations and assumptions

This does not convert timestamps, time zones or fractional-day times. The nonexistent 1900-02-29 is rejected and no valid date produces 1900-system serial 60. The systems cannot be inferred from a date string.

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 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. 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

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

Similar or alternative tools

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.
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.