Skip to content

How-to

How to Fix Dates Where the Day and Month Are Swapped

03/04/2024 is 3 April in most of the world and March 4 in the US. Find which order a column uses, convert it to an unambiguous format, avoid silent swaps.

The problem

“My dates are only wrong some of the time: 13/04/2024 is fine, but 03/04/2024 came out as March instead of April, and dates with a day above 12 turned into text.”

New to this? Read the lesson on day-first and month-first dates first.

Why it happens

The United States writes the month first (MM/DD/YYYY); most other countries write the day first (DD/MM/YYYY). For any date whose day is 12 or less, the same text is a valid date both ways: 03/04/2024 is 3 April or March 4, and the characters do not say which.

So whatever reads the text has to assume an order: Excel uses the computer’s regional setting, a database import uses its own default, and a script uses whatever its library does. When the assumption is wrong, the damage is uneven:

  • Dates with a day of 12 or less are silently swapped. They look like perfectly good dates.
  • Dates with a day above 12 fail, become text, or are rejected, because month 13 does not exist.

That is why a column can look mostly right, sort strangely, and give totals for the wrong months. About 40 % of the days in a year have a day of 12 or less, so the swap affects a large share of the rows, not an occasional one.

Fix it on Date And Time Converter

  1. Decide the order from the data, not from the screen. Look for any value with a number above 12 in the first or second place; it can be read only one way.
  2. For one value, type it into the converter and choose the source format yourself: DD/MM/YYYY or MM/DD/YYYY. When a value reads both ways and you have not chosen, the converter shows the reading for your locale and offers the other one beside it.
  3. For a column, open the file in the file workspace. It reads the whole column: a value such as 25/12/2024 is evidence for day-first, and the workspace picks the order that the evidence supports. If nothing settles it, it asks you, and an order the column contradicts is refused rather than applied.
  4. Write the result as YYYY-MM-DD so the question never comes up again.
Worked example: Read day first (DD/MM/YYYY): 3 April.
Input
03/04/2024 read as DD/MM/YYYY
Result
2024-04-03 YYYY-MM-DD · YYYY-MM-DD · clock kept as written · no timezone conversion

Open in the converter: 03/04/2024 → 2024-04-03

Worked example: The same text read month first (MM/DD/YYYY): March 4.
Input
03/04/2024 read as MM/DD/YYYY
Result
2024-03-04 YYYY-MM-DD · YYYY-MM-DD · clock kept as written · no timezone conversion

Open in the converter: 03/04/2024 → 2024-03-04

A whole column?

Open the CSV in the file workspace: pick the column, the formats and the zones, and the first 8 rows are converted at once, with counts for the whole file. The file is read in your browser and never uploaded. Downloading the converted file is part of Pro ($9.99/month).

Open the file workspace

In Excel, Google Sheets or code

Excel: re-import with the order stated

If the original file is still available, import it with Data › From Text/CSV, choose Transform Data, then on the date column use Change Type › Using Locale and pick Date with a locale that writes the day first (English (United Kingdom), for example) or month first (English (United States)). Power Query then reads every row in that order.

Excel: repair swapped cells in place

If the column is already split into real dates (swapped) and text (the ones that failed), this formula rebuilds the day-first date for both kinds when the original was DD/MM/YYYY:

=IF(ISNUMBER(A2), DATE(YEAR(A2), DAY(A2), MONTH(A2)),
    DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)))

The first branch swaps day and month back on cells Excel turned into dates; the second reads the text cells. It assumes zero-padded DD/MM/YYYY text; check a few rows before relying on it.

Code: say the order

# pandas: never let it guess
pd.to_datetime(df["date"], format="%d/%m/%Y")

-- PostgreSQL: set how dates are read for this session
SET datestyle = 'ISO, DMY';

Passing dayfirst=True to pandas is only a hint; an explicit format is a rule and raises an error on any row that does not fit.

Pitfalls

  • A column that mixes orders. If people in two countries typed into one sheet, no rule fixes 03/04/2024: only the person who typed it knows. Split the rows by source if you can.
  • Two-digit years. 03/04/24 adds a third question: is 24 the year, or the day of a year-first date? Treat it as its own format and choose the century deliberately.
  • Fixing twice. Run a swap repair once. A second pass swaps the dates back.

Questions

How can I tell which order a column uses?

Find a value with a number above 12 in the first or second place. 13/04/2024 can only be day first and 04/13/2024 only month first. If every row came from one system, one such value settles the column.

Why did dates with a day above 12 become text in Excel?

Excel read the column in your regional order. With month first, 13/04/2024 has no month 13, so it stays text, while 03/04/2024 is silently read as March 4. Both halves of the column are now wrong in different ways.

What if one column mixes both orders?

Then 03/04/2024 cannot be decided from the text; only the person or system that wrote it knows. The workspace converts one format per run and counts the rows it could not read; after a Pro run its report also lists them, so you can treat the rest separately.

Which format avoids the problem?

YYYY-MM-DD (2024-04-03) or a written month (3 Apr 2024). Neither can be read with the day and month swapped.