CSV date 03/04: March 4 or April 3?

03/04/2026 means March 4 in month/day/year order and April 3 in day/month/year order. A successful date conversion does not prove that the date has the intended meaning. Check the sender's format before converting a column.

Why it happens

CSV fields can be read as raw strings. Their interpretation as dates depends on the format specified or inferred by the receiving application. In the Python example below, datetime.strptime uses an explicit format: %d for day, %m for month and %Y for a four-digit year. Python documentation

Try three synthetic dates

Download the sample dates. This is synthetic data, not a customer export.

A column may contain mixed formats. One unambiguous value does not resolve the meaning of every other value.

Preserve the source, confirm the format, then separate failures

Make a copy first. Ask the sender for the export specification or date-order setting. This example assumes the sender has confirmed day/month/year. It does not infer that rule automatically from the sample.

Put the downloaded file in your working directory and run:

import csv
from datetime import datetime

with open('guide-12-dates.csv', encoding='utf-8-sig', newline='') as f:
    rows = list(csv.DictReader(f, strict=True))

first = rows[0]['date_text']
print('month/day/year:', datetime.strptime(first, '%m/%d/%Y').date().isoformat())
print('day/month/year:', datetime.strptime(first, '%d/%m/%Y').date().isoformat())
for row in rows:
    raw = row['date_text']
    try:
        value = datetime.strptime(raw, '%d/%m/%Y').date().isoformat()
    except ValueError:
        value = 'check source'
    print(row['record_id'], raw, value)

Expected output:

month/day/year: 2026-03-04
day/month/year: 2026-04-03
D01 03/04/2026 2026-04-03
D02 13/04/2026 2026-04-13
D03 31/02/2026 check source

The code does not overwrite the CSV. Keep failed rows for review. If the source mixes formats, obtain a justified rule for the affected rows before applying a bulk conversion.

Check structure separately from date meaning

The CSV checker can help inspect raw values and column structure. It does not determine day/month order or validate these values against the calendar. A CSV containing a wrongly interpreted date can still pass a structure check.

Resolve date meaning before using dates for deduplication, ordering or totals. Do not confuse a tool successfully opening the file with a verified conversion of its contents.

Limits and review notes

Reviewed: 2026-10-19. Both interpretations of the first date, the specified-format results, invalid-date handling and source preservation were checked with local Python. Excel date import, Excel cell types, browser-interface and download behavior were not tested. This does not claim that Excel will infer dates as this Python example does.

Times, time zones, Excel serial dates, two-digit years, whitespace normalization and mixed-format inference are outside this example.

AI assistance: AI helped draft the guide and synthetic example. The code was executed locally. No customer files were used, and no human Excel review is claimed.