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.

Description

A date such as 03/04/2026 can mean March 4 or 3 April.

A date such as 03/04/2026 can mean March 4 or 3 April. Sampling a column can eliminate incompatible formats, but dates that fit both day/month orders should retain that ambiguity.^[1]

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.

When to use Date Format Detection From Column Sample

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

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 Date Format Detection From Column Sample 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 values using the field table and calculation rules, then run the tool.
Read formats, ambiguous, observedCount. 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
values	Values	list of string; maximum length: 10000; minimum entries: 0; maximum entries: 10000	Required

Provide sample strings. The tool trims boundaries, ignores blank cells and tests exact Gregorian YYYY-MM-DD, two-digit MM/DD/YYYY and two-digit DD/MM/YYYY against every remaining value. It returns all compatible formats in that order and the nonblank observedCount. No observed values, or no common format, yields an empty list and ambiguous true; two surviving slash formats remain ambiguous.

formats = formats valid for every nonblank sampled cell; ambiguous = formats.length ≠ 1

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

Output field	Meaning	Value type
formats	Formats	list of string
ambiguous	Ambiguous	boolean
observedCount	Observed count	integer; minimum: 0; maximum: 9007199254740991

json

{
  "input": {
    "values": [
      "31/12/2024",
      "29/02/2024"
    ]
  },
  "output": {
    "formats": [
      "DD/MM/YYYY"
    ],
    "ambiguous": false,
    "observedCount": 2
  }
}

Limitations and assumptions

Only these three whole-date spellings and years 0001–9999 are supported. It does not guess a locale, accept time components or infer a format from blank cells. A sample cannot prove that every value in an unsupplied column follows the same format.

Numeric inputs must be finite. Follow the calculation-specific rules for overflow, underflow and exceptional cases. Cell-string lists also have a combined text budget of 100,000 UTF-16 units per list. Missing required fields, extra fields, wrong value types and violations of the calculation rules are input errors. A successfully returned false flag is an evaluated outcome; its meaning is explained above.

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

References

Date Format Detection From Column Sample — tool.io

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