Skip to content
Dates That Don't Break: Days, Months, and Times in Your Data
Start free

Why does pandas mix up the day and the month? Ambiguous dates and ISO 8601

The same text can be two different dates. Learn how to tell which one it is, and how to write dates nobody can read backwards.

Free course. Your Maestro, an AI, .

The same text, two dates

In Mexico people write the day first: 05/02/2026 is February 5. In the United States the month goes first, and the same text is May 2. The text itself doesn't say which one it is; only the person who wrote it knows.

A program that gets that text has to choose. If you don't tell it how to read it, it chooses for you. Predict what pandas chooses.

Three short programs. Write what you think each one prints and why, then run it.

Two February dates, no format

Example 1 of 3

sin_formato.py
import pandas as pdfechas = pd.Series(["05/02/2026", "06/02/2026"])print(pd.to_datetime(fechas))

A guess is fine. What you write stays in this browser and waits in your course.

Example 2 of 3, opens once you explain this one

Silence or an error

The first two programs show the two ways an ambiguous date can end:

  • Silence. If every number fits the format the program chose, it converts everything, wrong, without telling you. A file that only has the first twelve days of each month gets converted backwards from top to bottom.
  • An error. If a number shows up that doesn't fit, like a 13 where the program expected a month, it stops.

Silence is the dangerous case, because nothing forces you to check. Ask your Maestro whether one of your files could have been converted backwards.

How to tell what format your column has

Don't guess from one row. Look at the whole column and search for a number greater than 12: a month never goes past 12, so a valid value greater than 12 points to the day. That does not prove the whole column uses one format: values greater than 12 in both positions can mean mixed formats or invalid values. Set those aside to review and confirm the format with whoever exported the file; do not decide ambiguous rows by majority vote.

  1. Check every value in the column

  2. Where is there a number greater than 12?

    • Path 1 of 3: only in the first

      1. Day first

        format dd/mm/yyyy

      Continues at: Write the explicit format when you convert

    • Path 2 of 3: only in the second

      1. Month first

        format mm/dd/yyyy

      Continues at: Write the explicit format when you convert

    • Path 3 of 3: in neither

      1. Can't tell

        ask whoever exported the file

      Continues at: Write the explicit format when you convert

  4. Write the explicit format when you convert

Before using this diagram, confirm there is one format. Values greater than 12 in both positions: check for mixed formats or invalid values.

Try it

The code splits each date in ventas_exportadas into its three numbers and counts how many rows have a first number greater than 12. Add the count for the second number and decide what format the file is in.

A good answer prints two counts: one above zero and one at zero. That tells you which number is the day.

Your turn

Count how many dates in ventas_exportadas have a first number greater than 12 and how many have a second number greater than 12. What format is the file in?

Data: ventas-exportadas, 29 rows
The exercise's data, "ventas-exportadas". Your edits run with your code.
RowRemove
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Page 1 of 2

In the Café Altura file, 13 of the 29 dates have a first number greater than 12 and none has a second one. The first number is the day: the file is dd/mm/yyyy, as you'd expect from a register in Mexico. The data is consistent with that reading, and the source of this fixture confirms a uniform format. In a new file, that count alone does not rule out mixed ambiguous rows.

ISO 8601: year, month, day

The format nobody reads backwards is the one from the ISO 8601 standard: 2026-02-05. A four-digit year, a two-digit month, a two-digit day, separated by hyphens. It has two advantages:

  • Nobody reads it backwards: the year goes first, and no convention puts the day in the middle.
  • It sorts correctly as text, as you saw in the third program.

Python uses it when it prints a date: date.isoformat() returns YYYY-MM-DD. DuckDB displays dates the same way, and pandas writes them like that to a CSV. When you save or share dates, save them like this. If your team uses another format and you're not sure how to win them over, talk it through with your Maestro.

Check yourself, 1 of 2: The source confirms this column uses one format and contains valid dates: 03/04/2026, 15/04/2026, 07/05/2026, and 22/05/2026. What format is it in?