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
- 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.
- For one value, type it into the converter and choose the source format yourself:
DD/MM/YYYYorMM/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. - For a column, open the file in the file workspace. It reads the whole column: a value such as
25/12/2024is 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. - Write the result as
YYYY-MM-DDso the question never comes up again.
- Input
03/04/2024read as DD/MM/YYYY- Result
2024-04-03YYYY-MM-DD · YYYY-MM-DD · clock kept as written · no timezone conversion
- Input
03/04/2024read as MM/DD/YYYY- Result
2024-03-04YYYY-MM-DD · YYYY-MM-DD · clock kept as written · no timezone conversion
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).
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/24adds 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.