How to convert text to dates with pandas in Python

Dates stored as text are one of the most common sources of errors in pandas analyses: wrong sorting, filters that return nothing and charts that make no sense. Converting text to dates with pandas in Python fixes the problem and unlocks date-range filters, monthly grouping and date difference calculations. The route is always the same: pd.to_datetime(), with the right format.

Prerequisites

• Python 3.9 or later installed
• pandas installed (pip install pandas)
• An editor or notebook (VS Code, Jupyter, Google Colab)
• Basic knowledge of DataFrames

Step 1: Create a sample DataFrame

Let us start from a realistic case: a list of orders where the date came from a CSV and therefore arrived as text. Note that one row holds an invalid value — that is intentional, because this is exactly what happens with real data.

import pandas as pd

dados = {
    "encomenda": [1001, 1002, 1003, 1004],
    "data": ["01/03/2026", "15/03/2026", "02/04/2026", "sem data"],
    "valor": [120.5, 80.0, 210.0, 45.0],
}

df = pd.DataFrame(dados)
print(df.dtypes)

The output shows data object. In pandas, object means text. While the column stays like this, you cannot sort it chronologically or filter it by month.

Step 2: Convert text to dates with pd.to_datetime

The pd.to_datetime() function does the conversion. Because the dates use the European format (day first), we pass dayfirst=True.

df["data"] = pd.to_datetime(df["data"], dayfirst=True)

If you run this as it is, you will get an error similar to ValueError: time data "sem data" doesn't match format. That is the expected behaviour: by default, pandas would rather fail than invent a date. The next step solves it.

Step 3: Handle errors with errors="coerce"

With errors="coerce", anything that is not a valid date becomes NaT (the "missing value" for dates) instead of breaking the script.

df["data"] = pd.to_datetime(df["data"], dayfirst=True, errors="coerce")
print(df)

Row 1004 ends up as NaT and the others are converted. Nothing is lost: the problem becomes visible instead of hidden.

Step 4: State the exact format (faster and safer)

When you know the source format, pass it with format. It is faster on large files and avoids ambiguous readings, such as 03/04 being read as 4 March instead of 3 April.

df["data"] = pd.to_datetime(
    df["data_original"],
    format="%d/%m/%Y",
    errors="coerce",
)

The most used codes are %d (day), %m (month), %Y (4-digit year) and %H:%M:%S (time). For a value such as 2026-03-01 14:30:00, the format is "%Y-%m-%d %H:%M:%S".

Step 5: Find the rows that failed

Before moving on, look at the rows pandas could not convert. There is often a pattern (a different separator, a header repeated in the middle of the file).

problemas = df[df["data"].isna()]
print(problemas)
print("Linhas por converter:", len(problemas))

Step 6: Extract year, month and day with the .dt accessor

Once the column really is a date, the .dt accessor gives you every calendar part — this is where the conversion pays off.

df["ano"] = df["data"].dt.year
df["mes"] = df["data"].dt.month
df["dia_semana"] = df["data"].dt.day_name()
df["mes_ano"] = df["data"].dt.to_period("M")

print(df[["encomenda", "data", "ano", "mes", "dia_semana"]])

Step 7: Filter and group by period

With proper dates, filtering by range and summing by month becomes trivial.

inicio = pd.Timestamp("2026-03-01")
fim = pd.Timestamp("2026-03-31")

marco = df[(df["data"] >= inicio) & (df["data"] <= fim)]
print(marco)

por_mes = df.groupby(df["data"].dt.to_period("M"))["valor"].sum()
print(por_mes)

Check the result

Run three quick checks:

• df.dtypes should show datetime64[ns] for the data column (no longer object).
• df["data"].isna().sum() tells you how many dates were not converted — ideally 0, or a number you can explain.
• df["data"].min() and df["data"].max() should return plausible dates. A minimum in 1970 or a maximum in 2073 is a sign of a misread format.

Conclusion

With pd.to_datetime(), format and errors="coerce" you can already turn any text column into a reliable date column, spot the problematic records and group by period. The natural next step is to explore df.resample("M") for time series or pd.date_range() to build a full calendar and detect days without sales. Final tip: if you always import the same file, save yourself the work and convert it while reading, with pd.read_csv("ficheiro.csv", parse_dates=["data"], dayfirst=True). Do you already know the exact date format used in your files?