Fixing Date Formats in Pandas

Dates are the single most inconsistent kind of data you will meet. One column can hold 2026-07-16, 16/07/2026, July 16, 2026 and 07-16-26, all meaning the same day, and all stored as plain text. As strings they sort alphabetically — so 12/2024 lands before 02/2025 — and you cannot do any date math on them. Fixing date formats means parsing those strings into a real datetime type so they sort chronologically, support subtraction and filtering, and let you extract parts like month or weekday.

This is the date-specific case of fixing data types; the general type-conversion habits there apply here too. It builds on the overview in what is data cleaning.

The core tool: to_datetime

pd.to_datetime is the workhorse. It recognizes many common formats automatically and returns a datetime64 column. Two arguments make it robust: errors="coerce" turns any value it cannot parse into NaT (the datetime version of missing) instead of crashing, and dayfirst=True tells it to read ambiguous dates as day/month/year.

That ambiguity is the crux. 03/04/2026 is April 3 in US-style month/day order but 4 April in the day/month order used across India, the UK and much of the world. pandas cannot know which your source meant, so you must tell it.

Worked example

Here is an illustrative sample mixing several formats and one bad value.

import pandas as pd

df = pd.DataFrame({
    "event": ["signup", "login", "purchase", "refund", "error"],
    "raw_date": ["2026-01-05", "05/01/2026", "March 2, 2026",
                 "2026/03/20", "not a date"],
})

# day/month/year source; coerce the unparseable value to NaT
df["date"] = pd.to_datetime(df["raw_date"], dayfirst=True, errors="coerce")

print(df)
print("\nunparsed (NaT):", df["date"].isna().sum())

Expected output:

      event       raw_date       date
0    signup     2026-01-05 2026-01-05
1     login     05/01/2026 2026-01-05
2  purchase  March 2, 2026 2026-03-02
3    refund     2026/03/20 2026-03-20
4     error     not a date        NaT

unparsed (NaT): 1

Four different string formats all parsed into a clean datetime64 column, the two January entries correctly agree on the 5th because dayfirst=True read 05/01/2026 as 5 January, and the junk value became NaT instead of stopping the conversion. Now the column sorts and computes properly.

Extracting date parts

Once a column is a real datetime, the .dt accessor unlocks the analysis you actually need:

good = df.dropna(subset=["date"]).copy()
good["year"]    = good["date"].dt.year
good["month"]   = good["date"].dt.month
good["weekday"] = good["date"].dt.day_name()
print(good[["event", "date", "year", "month", "weekday"]])

      event       date  year  month    weekday
0    signup 2026-01-05  2026      1     Monday
1     login 2026-01-05  2026      1     Monday
2  purchase 2026-03-02  2026      3     Monday
3    refund 2026-03-20  2026      3     Friday

Grouping events by year, month, or weekday — the basis of nearly every time trend — only works after conversion. That is the payoff for fixing the format.

Using a known format

When every value follows one exact pattern, pass format explicitly. It is faster and removes all ambiguity:

s = pd.Series(["03-04-2026", "10-11-2026"])
parsed = pd.to_datetime(s, format="%d-%m-%Y")   # 3 Apr, 10 Nov — never misread

%d day, %m month, %Y four-digit year, %y two-digit year, %H:%M time. Specifying the format is the safest choice whenever you know it.

Two-digit years and other traps

Two-digit years like 26 are ambiguous — is it 1926 or 2026? pandas applies a pivot rule, but you should not rely on guesswork for important data. Where possible, get four-digit years from the source, or pin the interpretation with an explicit format and a sanity check on the resulting range. Another common trap is a column that is mostly dates but contains a few labels like "pending" or "TBD"; errors="coerce" turns those into NaT, which is usually the right behaviour, but you should confirm the count of NaT matches the number of non-date labels you expected rather than a parsing bug.

Time zones are a related concern. A plain datetime has no zone, so if your data spans regions, two events recorded at "09:00" may not be the same moment. For most beginner analytics you can work in a single assumed zone, but be aware that mixing zones without noting it produces subtly wrong ordering and duration math. Keep the assumption documented alongside the cleaning code.

Computing durations

The real reward of proper datetimes is arithmetic. Subtracting two datetime columns yields a timedelta you can convert to days, which powers metrics like time-to-purchase or account age.

d = pd.to_datetime(pd.Series(["2026-01-05", "2026-02-20"]))
s = pd.to_datetime(pd.Series(["2026-01-01", "2026-01-30"]))
print((d - s).dt.days.tolist())

[4, 21]

None of this works while the values are strings, which is the whole reason converting formats early pays off across the rest of an analysis.

How analysts use it

The routine is: convert every date column with to_datetime immediately after import, decide dayfirst based on the data's origin, and use errors="coerce" so a few bad rows do not block the batch. Then check isna().sum() on the new column — those NaT values are the dates that failed to parse and need attention. From there, .dt extraction feeds time-series grouping, cohort analysis, and any "trend over time" chart. Getting dates into a real type early prevents a whole class of silent bugs where filtering > '2025-01-01' on strings quietly returns the wrong rows.

Common mistakes

• Leaving dates as strings. They sort alphabetically and reject date math. Convert before any time-based work.
• Ignoring dayfirst. Reading day/month data with default month/day order silently swaps days and months for the first twelve days of every month — a subtle, damaging error.
• Not coercing errors. A single bad value makes the whole conversion raise. Use errors="coerce" and then inspect the resulting NaTs.
• Forgetting the NaTs. Coerced failures become missing values you still have to handle; do not let them slip through unexamined.

In interviews

A classic prompt is "This date column sorts wrong — why?" The answer: the dates are strings, so they sort lexically; convert with pd.to_datetime. Expect follow-ups on ambiguity ("how does pandas know 03/04 is March or April?") where you explain dayfirst, and on robustness where you mention errors="coerce" and NaT. Showing the .dt.year/.dt.month extraction demonstrates you understand why the conversion matters — it is the gateway to time-series analysis.

Where this fits in your learning path

Fixing date formats is a focused, high-value skill in the data cleaning cluster and a specialization of fixing data types. It rests on the cleaning fundamentals in what is data cleaning. Because so much analysis is time-based, confident date handling is a recurring part of the data analyst roadmap.