Handling Date Columns in CSV-to-JSON Conversion

Date Handling in CSV to JSON Conversion

Dates are one of the most problematic data types in CSV because there is no standard format. The same date can appear as 01/02/2024, 2024-01-02, Jan 2, 2024, or 45293 (Excel serial date).

Common date formats in CSV files

Format	Example	Origin
ISO 8601	2024-01-15	APIs, databases
US format	01/15/2024 or 1/15/2024	US spreadsheets
European format	15/01/2024 or 15.01.2024	European spreadsheets
Long format	January 15, 2024	Report exports
Excel serial	45306	Excel number format
Unix timestamp	1705276800	Databases, logs
ISO with time	2024-01-15T09:30:00Z	API responses

The ambiguity problem

The date 01/02/2024 means:

• January 2, 2024 in the US (MM/DD/YYYY)
• February 1, 2024 in Europe (DD/MM/YYYY)

Without knowing the source locale, it is impossible to parse this correctly. Dates between the 1st and 12th of any month are always ambiguous in XX/XX/XXXX format.

Recommended JSON output formats

When converting dates to JSON, standardize on ISO 8601:

{
  "createdAt": "2024-01-15",
  "updatedAt": "2024-01-15T09:30:00Z",
  "birthYear": 1990
}

ISO 8601 is unambiguous, sortable as a string, and universally recognized by JSON parsers and databases.

Excel serial date conversion

Excel stores dates as the number of days since January 0, 1900 (with a leap year bug for February 29, 1900, which did not exist):

function excelSerialToDate(serial) {
  // Excel epoch: January 0, 1900 (Dec 31, 1899)
  // Adjust for the famous Excel leap year bug (serial > 60)
  const adjusted = serial > 60 ? serial - 1 : serial;
  const epoch = new Date(1899, 11, 31);
  return new Date(epoch.getTime() + adjusted * 86400000);
}

Timezone considerations

CSV dates without timezone information are typically assumed to be in the local timezone of the data source. When converting to JSON for a web application, always:

1. Determine the source timezone (from metadata or user input)
2. Convert to UTC
3. Output with explicit timezone offset or Z suffix