Date parsing

This is the exact file agents read, including the credit header and licence notice. Save it as date-parsing/SKILL.md.

---
name: date-parsing
description: Parse messy date and time strings into real datetime values so they sort, filter and subtract correctly, across pandas, SQL and R. Covers the day-first versus month-first ambiguity, specifying an explicit format over guessing, ISO8601 and mixed-format columns, errors="coerce" with a NaT count, two-digit years, Unix timestamps, time zones (naive versus aware and the mixed-offset error), the out-of-range limit, and the NaT comparison trap. Use it when someone says "parse these dates", "convert to datetime", "to_datetime", "my dates are in the wrong order", "day and month are swapped", "mixed date formats", "dayfirst", "why are my dates NaT", "timezone error", or "these are Unix timestamps". Do NOT use it for non-date type conversion (see data-type-conversion), for standardising non-date text (see text-standardisation), for filling missing dates (see handling-missing-data), or for time-series resampling and analysis (see time-series-exploration).
license: Free to use and adapt with credit to Afeez Adebayo (https://afeezadebayo.com). Link back to this skill at https://afeezadebayo.com/skills-md/data/data-cleaning-preparation/date-parsing/ and to https://www.linkedin.com/in/afeez-adebayo/.
---

# Date parsing

## 1. When to use this skill, and when not to

Use this skill when a column holds dates or times as text or numbers, and you need real datetime values before you can sort, filter, subtract or group by time. The signs are dates that sort as strings ("02/01" before "10/12" before "1/5"), a subtraction that fails because the column is text, a month and a day that look swapped, or a timezone error when you try to combine two sources.

The risk with dates is silent wrongness. A date can parse without error and still be months off, because "01/02/2024" is the second of January in one country and the first of February in another, and those two readings are thirty days apart. The job of this skill is to parse to the correct datetime on purpose, count what failed, and refuse to guess an ambiguous format.

Do not use it for three neighbouring jobs. Converting non-date columns to numbers, booleans or categories belongs in data-type-conversion. Standardising inconsistent non-date text belongs in text-standardisation. Resampling, rolling windows and other time-series analysis, once the column is already datetime, belongs in time-series-exploration.

## 2. Inputs the agent must have before starting

Ask for anything missing. An ambiguous date format is the one input you must never assume.

- The tool and version. pandas 2.0 changed mixed-format parsing and removed older options; say which version is in use.
- The source of the data and its locale. A UK export is usually day-first; a US export is usually month-first. If you cannot find out and the values are ambiguous, ask rather than guess.
- Whether the values carry a time zone, and which one the blanks should be read as. Mixing naive and aware values without a rule raises an error.
- The meaning of a non-date token. A blank, "0000-00-00", "N/A" or a sentinel may be missing, not a parse failure. Confirm before coercing it to NaT.
- The acceptable date range, so you can tell a real out-of-range date from a typo.

If the format is truly ambiguous and the source is unknown, state the two readings and the thirty-day gap between them, and ask which is correct before parsing.

## 3. The parsing workflow, step by step

1. Look at the raw values before parsing. Print a sample and the distinct lengths and separators. You are deciding one thing first: is the column all one format, all ISO, or a mix.

2. Prefer an explicit format over inference whenever you know it. Passing a format string (for example "%d/%m/%Y") is unambiguous and markedly faster than letting the parser infer per value. Inference is a convenience for clean, unambiguous input, not a substitute for knowing the format.

3. Resolve the day-first versus month-first question with evidence, not a default. The parser's dayfirst and yearfirst flags both default to false and are hints, not rules; the parser can ignore them when a value parses another way. So check the data: if any value has a first number above 12, the format is day-first; if any second number is above 12, it is month-first. If the column is internally consistent, set the format explicitly. If it is truly ambiguous and the source locale is unknown, stop and ask, because a wrong choice is silently thirty days out.

4. Match the method to the shape of the column.
   - All ISO 8601 but varying in precision (some with a time, some without): use format "ISO8601".
   - One known format across the column: pass that explicit format.
   - A real mix of formats: use format "mixed", paired with dayfirst, understanding that per-value inference is slower and riskier and should be verified.

5. Coerce failures to a sentinel and count them. Parse with errors set to coerce so unparseable values become NaT instead of raising, then count the NaT values and compare against the number of missing tokens you expected. If more failed than expected, print examples and stop; do not accept a column where a tenth of the dates silently became NaT.

6. Handle time zones deliberately. Decide whether the column should be naive (no zone) or aware (a fixed point in time). To combine sources or store safely, parse with utc set to true, which reads naive values as UTC and converts aware values to UTC; store in UTC and convert to a local zone only for display. A column that mixes offsets (for example across a daylight-saving change) or mixes naive and aware values raises a ValueError unless you pass utc, and can otherwise fall back to a slow object dtype.

7. Parse numeric timestamps with an explicit unit. A column of integers is usually seconds or milliseconds since 1970; set the unit (s, ms) rather than letting it be read as a calendar year. Avoid parsing epoch values held as floats, because float rounding can shift the result; convert to an integer type first.

8. Watch the range and the resolution. The default nanosecond datetime covers roughly the years 1677 to 2262; a value outside that range raises an out-of-range error or returns an object column of Python datetimes. For dates far in the past or future, coerce the bad ones to NaT, widen the resolution, or keep them as a period, and know which you chose.

9. Treat two-digit years with suspicion. A value like "24-01-05" is ambiguous about the century, and the common parser rule maps a two-digit year into a window around the present, roughly the past fifty years and the next forty-nine, so "68" may become 1968 while "67" becomes 2067. When the real range is known, parse with an explicit four-digit format or correct the century yourself rather than trusting the pivot. Where you control the read, parse dates as the file loads, rather than letting the column arrive as text and converting it later, because parsing at load avoids a round trip and keeps the type right from the start.

10. Verify, and never test a missing date with equals. Confirm the result dtype is a real datetime type, the NaT count is what you expected, and the minimum and maximum dates are sane (a stray "0202" instead of "2020" shows up as an impossible year). Check for missing dates with the is-null test, never with a comparison to NaT, because NaT equals nothing, not even itself, and NaT compared with any date returns false. If one row has a date you know for certain, parse that row first and confirm it comes out right; a single known-good anchor catches a wrong day-or-month format faster than any summary statistic.

## 4. Worked example, end to end

An orders file has an `ordered_at` column with values like "2024-01-05", "05/01/2024", "1/5/24", "2024-01-05T09:30:00+01:00" and some blanks, exported from a UK system.

Parsing naively would be dangerous: "05/01/2024" is ambiguous, and because the source is UK the intended reading is day-first (5 January), not month-first. Confirming the locale settles it. The column is a real mix of formats and includes a timezone-aware value, so:

```python
parsed = pd.to_datetime(df["ordered_at"], format="mixed",
                        dayfirst=True, utc=True, errors="coerce")
nat = parsed.isna().sum()
```

`nat` is 12, matching the 12 blanks, so nothing unexpected failed. A quick sanity check on the range catches a thirteenth problem that did parse: `parsed.min()` returns the year 0024, from a value typed "24-01-05", which should have been 2024. That row is corrected at source, not silently kept. For the dashboard, the stored UTC values are converted once to Europe/London with tz_convert, so the display matches what the UK team expects while the stored column stays unambiguous. The handover states the dtype is datetime64 with a UTC zone, 12 values are NaT and match the blanks, and one row was referred back for a typed-wrong year.

## 5. Templates you can use verbatim

Explicit, unambiguous parse (pandas):

```python
df["d"] = pd.to_datetime(df["d"], format="%d/%m/%Y", errors="coerce")
print(df["d"].isna().sum(), "failed to parse")
```

Ambiguity check before choosing dayfirst:

> Before parsing, split each date on its separator and report the maximum value in the first position and in the second position. If the first ever exceeds 12 the format is day-first; if the second ever exceeds 12 it is month-first. If neither does, tell me the source locale is needed and do not guess.

Timezone-safe mixed parse:

```python
s = pd.to_datetime(col, format="mixed", dayfirst=True, utc=True, errors="coerce")
# store s in UTC; for display: s.dt.tz_convert("Europe/London")
```

Post-parse report:

> Report: result dtype, count of NaT, whether that equals the expected missing count, and the minimum and maximum parsed dates. Flag any year before 1900 or after next year as a likely typo.

## 6. Failure modes and anti-patterns

- Letting the parser guess the day or month order. "01/02/2024" parses cleanly as either 2 January or 1 February, thirty days apart, and the wrong one is silently wrong. Spot it by checking the month distribution or a known date. Set the format from the source locale, or ask.
- Treating dayfirst as a guarantee. It is a hint the parser can override when a value parses another way. Spot it when some rows obey it and some do not. Pass an explicit format for a consistent column.
- Parsing without coercing or counting. A bad value either raises and stops the job, or, worse, the column quietly turns to object dtype. Spot it by checking the result dtype and the NaT count every time. Use errors set to coerce and count.
- Mixing time zones without a rule. Combining naive and aware values, or offsets that differ across a daylight-saving change, raises a ValueError or produces a slow object column. Spot it in the error text or the dtype. Pass utc to normalise, and store in UTC.
- Testing a missing date with equals. NaT equals nothing, so a filter like `col == pd.NaT` returns all false and misses every gap. Spot it when a missing-date filter returns nothing. Use the is-null test instead.

## 7. Quality bar before handover

- The result dtype is a real datetime type, confirmed, not object.
- The day-first or month-first decision was made from the source or the data, never guessed, and recorded.
- Parsing used coerce, and the NaT count was reported and matched the expected missing count.
- The minimum and maximum parsed dates were checked for impossible years.
- Time zones were handled on purpose: either all naive by design, or normalised to UTC with the display zone noted.
- Missing dates are tested with the is-null test, not a comparison to NaT.
- Numeric timestamps were parsed with an explicit unit, from an integer type, not a float.

## 8. Guardrails

- Never guess an ambiguous date format. A wrong day-or-month choice is silently thirty days out and corrupts every downstream total. When the source is unknown and the values are ambiguous, stop and ask.
- Do not drop rows that failed to parse without confirmation. Coerce to NaT, report the count, and let a person decide; dropping is a bulk, irreversible edit.
- Do not fabricate the source locale, a time zone or a format. If it is not known, say so and ask rather than inventing one.
- Do not invent dates to fill gaps. A value that will not parse is missing or wrong, never a plausible guess.
- PII: dates of birth and similar dates are personal data. Parse them locally, do not log the values, and do not send them to an external service.
- Cost: parsing is local and free, but inference per value is far slower than an explicit format; on a large column, pass the format you know rather than letting the parser work it out row by row.
- This skill parses dates; it does not decide the business calendar, the reporting time zone or the fiscal year. It surfaces those for a human.

## 9. Tool-specific appendix (pandas)

Fenced off from the tool-agnostic core above.

- `pd.to_datetime(s, errors="coerce")` turns unparseable values into `NaT`; `errors="raise"` (the default) stops on the first bad value.
- `format="ISO8601"` parses any ISO 8601 string even when the precision varies; `format="mixed"` infers each value individually and the docs call it risky, so pair it with `dayfirst` and verify. `infer_datetime_format` is deprecated and `errors="ignore"` is being removed; do not rely on either.
- `dayfirst` and `yearfirst` default to `False`, are "not strict but will prefer" that order, and `yearfirst` wins when both are set (matching dateutil). They are hints; an explicit `format` is the reliable, faster choice.
- `utc=True` returns timezone-aware UTC output: naive input is localised as UTC, aware input is converted. With `utc=False`, mixed offsets or a mix of naive and aware input raise `ValueError` and can yield an `object` column.
- `unit="s"` or `unit="ms"` reads integers as epochs relative to `origin` (default 1970-01-01). The docs warn that float input may be rounded; cast to integer first.
- Nanosecond `datetime64` spans about 1677 to 2262; outside that, expect `OutOfBoundsDatetime` or an `object` column, and use `errors="coerce"` or `Series.dt.as_unit()` to widen resolution.
- `NaT == NaT` is `False` and `NaT > any_timestamp` is `False`; test with `pd.isna()` or `Series.isna()`.
- In `read_csv`, `parse_dates=[...]` parses on load, and `date_format=` (pandas 2.0+) applies a known format there. SQL equivalents: `TO_DATE` / `TO_TIMESTAMP` with an explicit format mask, `TRY_CONVERT` to coerce, and `AT TIME ZONE` for zones. R: `lubridate::dmy`, `mdy`, `ymd` and `ymd_hms`, which fix the order by the function name.

## Sources

- pandas, pandas.to_datetime API reference, last verified 1 November 2026: https://pandas.pydata.org/docs/reference/api/pandas.to_datetime.html
- dateutil, dateutil.parser documentation (dayfirst, yearfirst and two-digit year handling), last verified 1 November 2026: https://dateutil.readthedocs.io/en/stable/parser.html
- Kanaries, pandas to_datetime practical guide (mixed formats, coerce, units, NaT comparisons), last verified 1 November 2026: https://docs.kanaries.net/topics/Pandas/pandas-to-datetime
- pandas issue tracker, to_datetime parsing of mixed and empty values, last verified 1 November 2026: https://github.com/pandas-dev/pandas/issues/50251
- Anthropic, Equipping agents for the real world with Agent Skills, last verified 1 November 2026: https://www.anthropic.com/engineering/equipping-agents-for-the-real-world-with-agent-skills