Stop Excel 2013 Swapping Day and Month in CSV Dates

Tell Excel the date order used by the file before it converts the column. For a day/month/year source, choose Date: DMY in step 3 of the Text Import Wizard. Changing the displayed date format afterward cannot correct an April date that was already interpreted as March.

First establish what the source means. 03/04/2026 could mean 3 April or March 4. Neither Excel nor a formula can infer the author’s intention from that string alone. Ask for the export specification, a confirmed example, or an unambiguous replacement export before converting an unknown source.

Practice with a known DMY source

Save the synthetic DMY date CSV without opening it directly in Excel:

Record,EventDate
D-01,03/04/2026
D-02,12/11/2026
D-03,13/04/2026
D-04,29/02/2024

For this file, the declared rule is two-digit day, two-digit month, four-digit year. The first two rows expose silent day/month swaps, the third has a day above 12, and the last is a valid leap-day date. Keep an untouched copy as your comparison source.

Direct CSV opening uses Excel’s default interpretation settings. An import wizard gives you a chance to select the column type first. Microsoft: importing text and CSV files.

Controlled import in Excel 2013 for Windows

1. In a blank workbook, choose Data > Get External Data > From Text and select the file. If that legacy command is unavailable on a customized ribbon, open a separate .txt copy through File > Open to reach the Text Import Wizard.
2. Choose Delimited, retain the header row, and select Comma as the only delimiter. Confirm two preview columns.
3. In the final wizard step, select the EventDate column. Choose Date, then DMY. This describes the order in the source, not how you want the result displayed.
4. Finish into an empty worksheet, with Record in A and EventDate in B. Widen column B if necessary.

For an MDY file choose MDY; for a YMD file choose YMD. Mixed orders in one column need separate handling. The wizard does not make an inherently ambiguous source unambiguous. Microsoft: Text Import Wizard.

Check the underlying date

For the imported values in B2:B5, put =DAY(B2) in C2 and =MONTH(B2) in D2; fill both down. Use DAY and MONTH on the converted date values, not on the raw ambiguous text.

The functions inspect date values independently of how the cells look. Microsoft DAY reference; MONTH reference.

For an English-language display, select B2:B5, press Ctrl+1, choose Custom, and enter dd-mmm-yyyy. D-01 should then display 03-Apr-2026. Format codes and month names can be localized, so the numeric DAY/MONTH checks are the more portable checkpoint. Save the verified workbook as .xlsx while retaining the original CSV.

A safer alternative: retain text, then build dates explicitly

If the source needs auditing, import EventDate as Text instead of Date. Keep that raw column unchanged and create a converted column beside it. For this exact fixed-width dd/mm/yyyy file, enter in C2:

=DATE(VALUE(RIGHT(B2,4)),VALUE(MID(B2,4,2)),VALUE(LEFT(B2,2)))

Fill down and format C as a date. The positions explicitly supply year, month, and day to DATE. RIGHT, MID, LEFT, and VALUE extract and convert the components. This formula is for the stated fixed-width pattern, not arbitrary date strings or timestamps.

Add a validation column. DATE normalizes out-of-range components: 31 February 2026 becomes 3 March 2026. A successful calculation is therefore not enough. In D2 use:

=IFERROR(IF(TEXT(C2,"dd/mm/yyyy")=B2,"OK","CHECK"),"CHECK")

Fill down. The four supplied rows should say OK. To test the guard in a separate practice row, enter the text '31/02/2026: the conversion normalizes it, but the round-trip comparison should say CHECK. A blank, an error, or a noncanonical string such as 3/4/2026 also needs review under this strict rule. Do not replace CHECK with a guessed date. DATE’s documented normalization explains why the guard matters; TEXT creates the comparison string and IFERROR keeps errors visible as CHECK.

These formulas use English function names, English date-format codes, and comma argument separators. Adapt localized names/codes and list separators if your Excel installation requires them. Where source formats vary, standardize or split the source using its documented rules before applying this fixed-position formula.

Recovering a wrong import

• A numeric date is already wrong: reimport from the untouched original. Switching from mm/dd/yyyy to dd/mm/yyyy changes presentation, not the day/month meaning already stored.
• The cell is still text: keep a copy. You can reimport with the correct order, or use the explicit conversion above if the source exactly matches its rule.
• The entire column is ambiguous and its convention is unknown: preserve the strings and get clarification. Do not use a majority vote or assume the computer’s locale matches the exporter.
• Some rows are DMY and others MDY: no single wizard setting can safely fix that column. Separate rows using trustworthy source metadata, then convert each group.
• Dates above 12 remain text or errors: suspect an order mismatch, inconsistent separators, spaces, or invalid dates. Compare with the raw file rather than repeatedly reformatting the cells.
• Only hashes appear: widen the column first. That is a display check, not proof that the date was parsed correctly.

Avoid a blanket DATEVALUE conversion for ambiguous text: parsing a date string still depends on which date convention is recognized. Also avoid swapping DAY and MONTH across every already-converted date; it can corrupt rows that were correct.

Microsoft 365: set the conversion locale

In modern Excel for Windows, use Data > From Text/CSV > Transform Data. Inspect Applied Steps: remove or replace an incorrect automatic date conversion so you are working from the original text, not from already-swapped dates. Right-click EventDate, choose Change Type > Using Locale, and select Date with English (United Kingdom) for this DMY fixture. Then load and repeat the day/month checks. Microsoft: data types and conversion locale.

This locale is for the source text’s interpretation; it does not require changing Windows settings for every application. The legacy wizard is also available in modern Windows Excel after enabling From Text (Legacy) in File > Options > Data, then using Data > Get Data > Legacy Wizards. Do not look for those modern enablement controls in Excel 2013. Wizard availability.

Version and verification notes

The legacy instructions target Excel 2013 for Windows, without requiring its separate Power Query add-in. The Microsoft 365 procedure uses the newer integrated editor; Mac and web menus are outside this walkthrough. Microsoft’s current import help lists newer supported editions, so it should not be mistaken for native Excel 2013 testing.

Not tested in Excel. Official references were checked October 16, 2026. Calendar dates, component values, and the invalid-date round-trip example were independently calculated outside Excel. No native screenshots or application execution are claimed.