How do I parse dates that come in several different formats?

The short version

Use pd.to_datetime with errors="coerce" so values that will not parse become NaT instead of raising, then count and inspect the failures. Specify format when you know it, because letting pandas guess is slower and can silently misread ambiguous dates.

Coercing bad values

import pandas as pd

s = pd.Series(["2021-01-01", "junk"])
parsed = pd.to_datetime(s, errors="coerce")

print(parsed.isna().tolist())   # [False, True]

The bad value becomes NaT (the datetime version of missing) rather than stopping the whole job. You can then find exactly which rows failed.

s[parsed.isna()]   # the values that could not be parsed

This is far better than a crash, because it tells you the scale and nature of the problem instead of just the first offender.

The ambiguity that silently corrupts

03/04/2021 is the 3rd of April in British convention and the 4th of March in American. Pandas cannot know which, and a wrong guess produces perfectly valid but wrong dates, with no error.

pd.to_datetime("03/04/2021", dayfirst=True)   # 2021-04-03

State dayfirst=True, or better, give the exact format.

pd.to_datetime(s, format="%d/%m/%Y", errors="coerce")

An explicit format is unambiguous and much faster on a large column, because pandas does not have to infer the pattern.

Genuinely mixed formats

If one column really contains several formats, parse in passes: try the most common format first, then fill the remaining NaT values with a second attempt.

dates = pd.to_datetime(s, format="%d/%m/%Y", errors="coerce")
dates = dates.fillna(pd.to_datetime(s, format="%Y-%m-%d", errors="coerce"))

Each pass is explicit and fast, and anything still NaT at the end is genuinely unparseable and needs a human decision.

Impossible dates

A date like 2021-13-01 has no thirteenth month, so it coerces to NaT rather than rolling over into the next year. That is correct behaviour, and counting these tells you how much of your source data is malformed.

Unix timestamps

Numbers are not dates unless you say what unit they are in.

pd.to_datetime(1609459200, unit="s")   # 2021-01-01

Without unit, a large integer is interpreted as nanoseconds, which produces a date in 1970 and looks like nonsense. This is a common confusion when reading exported logs.

Always check after parsing

Confirm the dtype and the range before trusting the column.

parsed.dt.year.value_counts()   # a year of 1970 or 2201 means a parsing error

Absurd years are the fingerprint of a unit or format mistake, and catching them here saves you from a chart with a data point in the wrong century.