Excel Swapping Day and Month? How to Fix Dates

If Excel has the day and month swapped, so 3 April shows up as 4 March, the cause is a clash between the date order in your data and your computer's regional settings. It usually happens with CSV files and data exported from other systems. This guide shows how to spot swapped dates, how to fix the ones Excel converted wrongly and the ones it left as text, and how to stop it happening again.

Why Excel swaps the day and month#

Excel reads dates in the order your computer's regional settings use. A computer set to the United States reads 03/04/2024 as month/day/year: 4 March. A computer set to the United Kingdom, India or Australia reads it as day/month/year: 3 April.

When the data was written in one order and your computer uses the other, Excel guesses wrong. It doesn't know where the file came from. This affects:

• Opening a CSV file directly.
• Pasting dates copied from a web page, email or another program.
• Typing dates in the other order.

Dates you create with formulas such as DATE, or pick from a real date cell, are never affected.

How to spot swapped dates#

Swapped dates are tricky because many of them look like valid dates. Look for these signs in a column imported as day/month/year on a month/day computer:

So a mix of left-aligned and right-aligned dates in one column is the classic sign. To check a cell, use =ISNUMBER(A2): TRUE means a real date, FALSE means text. =DAY(A2) and =MONTH(A2) show how Excel read a real date. The guide to YEAR, MONTH and DAY covers those functions.

Why changing the date format doesn't fix it#

Excel stores a date as a number and the format only controls how it looks. If Excel stored 4 March, a format of dd/mm/yyyy shows 04/03/2024 and mm/dd/yyyy shows 03/04/2024. Both still mean 4 March. Text dates don't change at all, because a format can't turn text into a date.

So you have to rebuild the dates. Which fix you need depends on whether the cells are text or wrongly converted dates.

Fix 1: Text to Columns for text dates#

Text to Columns re-reads text in the order you choose, so it's the quickest fix for a column of text dates. It's best done on the original data before any were converted.

1. Select the column of dates.
2. Go to Data > Text to Columns.
3. Choose Delimited and click Next, then Next again.
4. Under Column data format, choose Date and pick DMY (or the order your data uses).
5. Click Finish.

Excel converts each text date using that order. This doesn't help dates that Excel already converted wrongly, because they're numbers now, not text. For those, use Fix 3.

Fix 2: rebuild text dates with a formula#

When the text always has the same layout, such as 15/03/2024, pull out the parts and give them to DATE in year, month, day order:

=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))

RIGHT takes the last 4 characters (2024), MID takes 2 characters from position 4 (03), and LEFT takes the first 2 (15). The result is the real date 15 March 2024, which Excel stores as 45366. Format the cell as a date to see it.

This only works when every day and month has two digits. In Microsoft 365, TEXTSPLIT can split on the slash for layouts like 5/3/2024.

Fix 3: swap dates that were converted the wrong way#

If Excel turned 03/04/2024 into 4 March, the day and month are just in each other's place. Swap them back:

=DATE(YEAR(A2),DAY(A2),MONTH(A2))

DATE takes the year, then uses the old day as the month and the old month as the day. 4 March becomes 3 April.

For a mixed column, combine both fixes with IF:

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

Only use the swap on dates you know came from the other order. A date you typed correctly would be swapped into a wrong one. Once the helper column is right, copy it and paste as values over the original.

How to prevent swapped dates#

The best fix is to stop the swap happening:

• Import instead of opening. Use Data > From Text/CSV, then Transform Data. Right-click the date column and choose Change Type > Using Locale, then pick Date and the locale the file uses, such as English (United Kingdom).
• Type dates as year-month-day. Excel reads 2024-03-15 the same way on every computer.
• Build dates with DATE. =DATE(2024,3,15) is never ambiguous. The guide to Excel date functions shows the others.
• Show the month as a word. A format like dd-mmm-yyyy displays 15-Mar-2024, so a swap is obvious at a glance.

Why swapped dates break your formulas#

Swapped dates don't just look wrong. Every calculation built on them is wrong too. A delivery that took 5 days might appear to take 30, and month-end reports put orders in the wrong month.

DATEDIF shows it clearly. It needs the start date first and returns #NUM! if the start is after the end. Take an order dated 02/03/2024 (2 March) and delivered 10/03/2024 (10 March), read on a month/day computer:

• The dates become 3 February and 3 October, so =DATEDIF(B2,C2,"d") returns 243 days instead of 8, with no warning.
• An order dated 10/02/2024 (10 February) and delivered 01/03/2024 (1 March) becomes 2 October and 3 January. The start is now after the end, so DATEDIF returns #NUM!.

See the DATEDIF function guide for its units. Fixing the dates first keeps formulas for adding days and months and finding the first and last day of the month correct.

Practise it#

Practise working with dates in a live spreadsheet: you'll build dates with DATE, pull out the year, month and day, and do date maths, with each formula checked instantly. Start with the text-date exercise on this page.