Handling “Locale” errors: Importing DMY dates into an MDY system.

This problem occurs when dates formatted in a Day/Month/Year (DMY) pattern, standard across the UK, Europe, Australia, and parts of Latin America, are imported into an operating system or Excel installation configured for Month/Day/Year (MDY), which is standard in the United States. When this happens, Power Query either errors out with DataFormat.Error: We couldn't parse the input provided as a Date. or silently swaps the month and day numbers, corrupting your records.

Quick Answer

In Power Query, right-click the date column, select Change Type > Using Locale…, choose Date as the target Data Type, and select English (United Kingdom) (or your source data’s matching country) as the Locale. This forces Power Query to parse the text using the source system’s DMY structure rather than your local computer’s settings.

---

The Two Faces of Locale Date Errors

Locale mismatches cause two distinct failure modes:

1. Hard Parsing Errors (DataFormat.Error): When the source day is greater than 12 (e.g., 28/04/2026), an MDY system tries to read 28 as the month. Because a 28th month does not exist, the operation halts with an error.
2. Silent Data Inversion (The Hidden Danger): When the day is 12 or less (e.g., 03/05/2026), the system successfully parses the value without raising an error, but it interprets the date as March 5 instead of May 3.

Source Date: 03/05/2026 (May 3rd in DMY)
                     │
                     ▼
       [Imported into US (MDY) Excel]
                     │
                     ▼
Result: March 5th, 2026 ◄─── SILENT CORRUPTION (No Error Raised!)

Source Date: 25/05/2026 (May 25th in DMY)
                     │
                     ▼
       [Imported into US (MDY) Excel]
                     │
                     ▼
Result: DataFormat.Error ◄── Month 25 does not exist

Silent corruption is significantly more dangerous than a hard error, as it will distort accounting periods, lead time tracking, and aging analyses unnoticed.

---

How to Fix Locale Date Mismatches

Step 1: Remove the Default Auto-Generated “Changed Type” Step

When you import a CSV or text file, Power Query automatically inserts a Changed Type step using your local computer’s regional culture. This locks in the incorrect parsing immediately.

1. Open the query in the Power Query Editor.
2. In the Applied Steps pane, look for the early Changed Type step.
3. If this step converted your date column using the wrong format, click the red X to remove it.
4. Your date column should now display as raw text, showing an ABC icon in the column header.

For details on managing auto-applied data steps, see Auditing “Automatic Step Generation” that breaks future refreshes.

Step 2: Apply the “Using Locale…” Transformation

1. Right-click the header of the raw text date column.
2. Select Change Type > Using Locale… (located at the bottom of the submenu).
3. In the dialog box:
   • Set Data Type to Date (or Date/Time if your column contains timestamps).
   • Set Locale to a region that uses the DMY structure matching your input file. Common selections include English (United Kingdom), English (Australia), or German (Germany).
4. Click OK.

┌────────────────────────────────────────────────────────┐
│  Change Type with Locale                               │
├────────────────────────────────────────────────────────┤
│  Data Type:  [ Date                              ▼ ]   │
│  Locale:     [ English (United Kingdom)          ▼ ]   │
│                                                        │
│  Sample Input Values:                                  │
│  28/04/2026  ──►  4/28/2026 (Displays in Local Format) │
│                                                        │
│  [ OK ]                               [ Cancel ]       │
└────────────────────────────────────────────────────────┘

Power Query translates this action into the following resilient M-code:

= Table.TransformColumnTypes(#"PreviousStep", {{"OrderDate", type date}}, "en-GB")

The culture parameter "en-GB" ensures that regardless of who opens the workbook, whether on an American, Japanese, or British machine, the source column will always be interpreted using the Day/Month/Year standard before converting to the local user’s display format.

---

Complex Scenarios: Non-Standard Text Formats

If the “Using Locale” setting fails to recognize your date string (for example, with formats like 28-MAY-26 or dates containing embedded text codes):

Method A: Split by Delimiter

If dates use mixed delimiters (like slashes combined with periods), split the column into individual components before building the date:

1. Select the column, go to the Transform tab, and click Split Column > By Delimiter.
2. Split by the separator (/ or ).
3. Reorder the resulting day, month, and year columns into Year, Month, Day order.
4. Merge them using standard ISO format (YYYY-MM-DD).
5. Cast the merged column to Date.

Method B: Use Explicit Date Parsing Functions

You can write custom M-code to parse dates that have unusual patterns using Date.FromText:

= Table.AddColumn(#"PreviousStep", "ParsedDate", each Date.FromText([RawDateString], [Format="dd/MM/yyyy", Culture="en-GB"]), type date)

The explicit Format parameter forces the parsing engine to match characters directly:

• dd = two-digit day
• MM = two-digit month
• yyyy = four-digit year

---

Before You Finish: Set Global vs. Workbook Locale

If you consistently import files from overseas subsidiaries, you can set the regional default for the entire file:

1. In the Power Query Editor, click File > Options and settings > Query Options.
2. Under Current Workbook, select Regional Settings.
3. Set the Locale to your primary data source region (e.g., English (United Kingdom)).
4. This ensures that all future date and numeric type conversions in this specific file default to that region without manual intervention.

To resolve similar parsing issues with date-time offsets or database dates, see DataFormat.Error: We couldn’t parse the input provided as a Date and Troubleshooting “Locale” errors: Importing SQL YYYY-MM-DD into MDY Excel. For diagnosing general data typing mistakes, see Troubleshooting “Inferred Data Type” mistakes by Power Query.