How to Fix CSV Date Format in Excel When Day and Month Flip

To fix CSV date format in Excel, re-import the file with the correct locale instead of double-clicking it: in Get Data > From Text/CSV, set the date column's locale to DMY or MDY to match your file, then load. If the file is already open with flipped dates, convert the text with DATEVALUE or rebuild the date with the DATE function.

Date flipping is one of the most common CSV problems. A value such as 03/04/2026 can mean March 4 or April 3, and Excel has to guess which one you meant. When it guesses wrong, every date in the column is silently wrong — and because the result still looks like a date, nobody notices until a report, a deadline, or a payment is off.

Why CSV Dates Flip: DMY vs MDY

A CSV file stores dates as plain text. It does not record whether 03/04/2026 is day-first or month-first. Excel interprets the text using a convention, and two conventions dominate:

• DMY (day-month-year): 03/04/2026 means 3 April 2026. Used in most of Europe, the UK, Australia, India, Pakistan, and much of Asia.
• MDY (month-day-year): 03/04/2026 means March 4, 2026. Used in the United States.

When a CSV created under one convention is opened on a computer set to the other, Excel applies the wrong reading. Worse, the flip is not always visible: a value such as 25/04/2026 cannot be a month-first date, so Excel may leave it as text or reinterpret it unpredictably, while 03/04/2026 flips cleanly and looks correct. The result is a column where some dates are wrong, some are text, and the file looks fine at a glance.

Excel also has its own date system (days counted from a start date) and two variants — the 1900 system used by default on Windows and the 1904 system used by default on older Mac Excel. A file moved between those systems can shift all dates by four years. If your dates are exactly four years off, the date system, not DMY/MDY, is the cause.

Diagnose First: Are Your Dates Text or Real Dates?

Before fixing anything, find out what Excel actually holds:

1. Check alignment. In a default worksheet, real dates align right and text aligns left. A date column that aligns left is text, not a date.
2. Use ISNUMBER. In a helper column, enter =ISNUMBER(A2). TRUE means a real date value; FALSE means text.
3. Check sorting. Sort the column ascending. Real dates sort chronologically. Text dates sort alphabetically, so 01/02/2026, 01/03/2026, and 02/01/2026 will not be in calendar order.
4. Spot-check an ambiguous date. Find a date where the day is 12 or less, such as 03/04/2026, and ask: does this row's real-world date match March 4 or April 3? Check against the source system — an order confirmation, a bank statement, or the export tool.

If the dates are real date values but flipped, you need to reinterpret them. If they are text, you need to convert them with the right locale. The two cases need different fixes.

Method 1: Re-Import With the Correct Locale (Best Fix)

Re-importing is the cleanest fix because it corrects the dates at the source instead of patching them afterward. Never double-click the CSV — always import deliberately.

Step-by-Step: Get Data Import With Locale

1. Start with a blank workbook. Do not open the CSV directly.
2. Open Get Data. Go to Data > Get Data > From File > From Text/CSV and select your CSV file.
3. Open the Transform view. In the preview dialog, click Transform Data to open Power Query Editor, rather than loading immediately.
4. Set the date column type with locale. Select the date column, then go to Transform > Data Type > Date, and choose "Using Locale" if the menu offers it. Pick the locale that matches your file — for example, English (United Kingdom) for DMY, or English (United States) for MDY.
5. Check the preview. Confirm that an ambiguous date such as 03/04/2026 now shows the intended day and month.
6. Close and load. Click Close & Load. Excel loads the corrected dates as real date values.

Step-by-Step: Legacy Text Import Wizard

In older Excel versions, or when you prefer the classic wizard:

1. Open a blank workbook and go to Data > From Text/CSV (or Data > Get External Data > From Text in older versions).
2. Select the file and step through the wizard.
3. In step 3, set the date column format. Click the date column in the preview, then choose the format that matches your file: DMY, MDY, or YMD. This tells Excel exactly how to read the dates.
4. Finish and load. The dates arrive as real date values in the correct reading.

The wizard's step-3 setting is the single most reliable way to fix DMY/MDY confusion, because it removes all guessing.

Method 2: Power Query Locale on an Existing Import

If you already imported the CSV through Power Query, you can fix the date type without starting over:

1. Open the query. Go to Data > Queries & Connections, right-click the query, and choose Edit.
2. Select the date column and change its type to Date using the locale option, as in Method 1.
3. Refresh. Click Close & Load. The dates reload with the correct reading.

Power Query remembers the locale step, so future refreshes with a new CSV in the same format will apply the same correction automatically. For a recurring monthly export, this is the most maintainable fix — the guide to combining multiple CSV files with Power Query shows how Power Query keeps repeated imports consistent.

Method 3: Formula Fixes for an Already-Open File

If re-importing is not practical — for example, the file has already been edited — formulas can rebuild the dates. Work in a helper column so the original data stays intact until you verify the result.

DATEVALUE for Text Dates

If the dates are text in a consistent format, DATEVALUE converts them using your computer's locale:

=DATEVALUE(A2)

Format the helper column as a date. If DATEVALUE returns a #VALUE! error, the text format does not match your locale — use the DATE rebuild below instead.

DATE Rebuild for Flipped Real Dates

If the dates are real date values but flipped (day and month swapped), rebuild each date by swapping the parts back:

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

This takes the year from the flipped date, then uses the flipped day as the month and the flipped month as the day — restoring the original date. Check the result against a known row before applying it to the whole column.

TEXT for Display Fixes

If you only need the dates to display correctly (for a report or export) and the underlying values are text, TEXT can reformat them:

=TEXT(DATEVALUE(A2), "dd-mmm-yyyy")

This produces a text string in an unambiguous format such as 03-Apr-2026. Use it for display, not for further date math — for calculations, you need real date values from DATEVALUE or DATE.

Method 4: Prevent the Flip on Future Opens

Fixing the file once is good; stopping the problem from recurring is better:

• Use an unambiguous date format in the source. If you control the export, use ISO format yyyy-mm-dd (for example, 2026-04-03). It has no DMY/MDY ambiguity, and Excel reads it correctly in every locale.
• Always import, never double-click. Double-clicking a CSV lets Excel guess the locale. A deliberate import through Get Data or the Text Import Wizard lets you set it.
• Pre-format the column as Text. If you only need to view dates and never calculate with them, importing the date column as Text preserves the original string exactly, with no reinterpretation.
• Match the computer locale to the file. If your team regularly exchanges CSV files with partners in another region, agree on one date format — ISO is the safest choice — and document it.

If your CSV also has other import quirks, such as a semicolon delimiter or encoding problems, the guides to opening a semicolon-delimiter CSV and fixing CSV encoding errors cover those fixes.

Comparison Table: Which Date Fix Should You Use?

Re-importing is almost always better than patching with formulas, because it fixes the data instead of layering a correction on top of a misreading. Use formulas only when re-importing is genuinely impractical.

Common Mistakes With CSV Dates

Assuming the Flip Is Uniform

A column can contain a mix: some dates flipped, some left as text, some correct. A single blanket fix can make the mix worse. Always diagnose with ISNUMBER and spot-checks before applying a fix to the whole column.

Fixing Only the Display

Changing a cell's number format changes how a date looks, not what it is. A flipped date formatted as dd-mmm-yyyy still holds the wrong date — it just displays it more clearly. Fix the value, not the format.

Using Find and Replace on Dates

Swapping / characters or reordering date parts with Find and Replace is fragile: it can corrupt valid dates, break text dates, and leave a mix of formats. Use a proper import or a formula rebuild instead.

Forgetting the 1904 Date System

If all dates are exactly four years off, the workbook uses the 1904 date system. In Excel, go to File > Options > Advanced and check or uncheck "Use 1904 date system" to match the source. This is a workbook-level setting, so it affects every date in the file.

Trusting Ambiguous Dates Without a Spot Check

Any date where the day is 12 or less is ambiguous. Always verify at least one such date against the source system before sharing a converted file.

Frequently Asked Questions

Why does Excel flip day and month when I open a CSV?

Because a CSV stores dates as plain text with no locale information, and Excel must guess whether 03/04/2026 is DMY or MDY. It guesses using your computer's locale, which may not match the file's convention. Importing deliberately and setting the locale removes the guesswork.

How do I tell Excel a CSV uses DMY dates?

Import through Data > Get Data > From Text/CSV, open Transform Data, and set the date column's type with locale to a DMY locale such as English (United Kingdom). In the legacy Text Import Wizard, set the date column format to DMY in step 3.

Can I fix flipped dates without re-importing?

Yes, with formulas. If the dates are text, use =DATEVALUE(A2). If they are real dates with day and month swapped, rebuild them with =DATE(YEAR(A2), DAY(A2), MONTH(A2)). Work in a helper column, verify against a known row, then replace the original values.

Why are some CSV dates text and others real dates?

Excel converts a text date to a real date only when it can parse it under your locale. A value such as 25/04/2026 cannot be month-first, so under an MDY locale Excel may leave it as text, while 03/04/2026 converts — incorrectly — to March 4. The result is a mixed column. Re-importing with the correct locale converts the whole column consistently.

What is the safest date format for CSV exports?

ISO format yyyy-mm-dd (for example, 2026-04-03). It is unambiguous in every locale, sorts correctly as text, and Excel reads it as a real date without guessing. If you control the export, use ISO dates to eliminate DMY/MDY problems entirely.

Final Checklist

Before you share a file with fixed dates, run through this short checklist:

• Dates are real date values (ISNUMBER returns TRUE).
• An ambiguous date spot-checks correctly against the source system.
• The column sorts in true calendar order.
• No dates are off by exactly four years (1904 system check).
• The fix is applied to the whole column, not just part of it.
• Future imports will use ISO dates or a deliberate import with locale.
• The original CSV is saved as a backup before any destructive fix.

If all seven checks pass, your dates are correct — and the next import will not flip them again.