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

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 pd

fechas = pd.Series(["05/02/2026", "06/02/2026"])
print(pd.to_datetime(fechas))

What dates do you think pandas prints?

Why?

A guess is fine: it's kept as you wrote it, so you can compare. Only you and your Maestro see your predictions.

Example 2 of 3, opens once you explain this one

Make your prediction

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 wherever that number shows up, that's the day.

Check every value in the column
Where is there a number greater than 12?
- Path 1 of 3: in the first number
  1. Day first
    format dd/mm/yyyy
  Continues at: Write the explicit format when you convert
- Path 2 of 3: 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
Write the explicit format when you convert

Check the whole column, not one row.

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.

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?

Your code (Python)

Run your code to see what it prints and the value of its last expression. Every run starts fresh. Ctrl+Enter runs it. Tab indents; to leave the editor, press Escape, then Tab.

Ready to run your code.

Run your code to see its output here.

Data: ventas-exportadas, 29 rows

Enter saves a cell and Escape leaves it as it was. Leave a cell empty for a missing value.

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

Page 1 of 2

Run it yourself

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. Now you know it from the data, not from an assumption.

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.

datetime: basic date and time types (Python documentation, in Spanish)

pandas.to_datetime: dayfirst and errors (pandas documentation)