Troubleshooting “Locale” errors: Importing SQL YYYY-MM-DD into MDY Excel.

October 31, 2026 7 min read

When importing SQL Server dates into an Excel workbook configured for US Month-Day-Year (MDY) regional settings, date parsing often breaks. In SQL databases, standard dates follow the ISO 8601 format (YYYY-MM-DD). If this data passes into Excel as raw text or through an ambiguous data stream, Excel struggles to map four-digit years and hyphen-separated values to the operating system’s native regional format.

This mismatch produces left-aligned text strings that cannot calculate, silent day-and-month transpositions on days 12 and below, or parsing errors in Power Query.

Quick Fix: Import ISO Dates into MDY Excel

In Power Query, right-click the date column header, select Change Type > Using Locale…, set the Data Type to Date, and choose English (United States) or English (Canada) as the locale. Alternatively, convert the column at the database level in your extraction query using CONVERT(VARCHAR(10), YourDateColumn, 101) to output native MDY text directly.

Why SQL Dates Fail in MDY Excel

The root of this issue lies in the difference between how relational database engines store dates and how the Windows operating system interprets user data.

  1. Internal Storage vs. Text Representation: SQL Server stores DATE and DATETIME columns as binary integer pairs, not formatted text. When a direct query runs through a modern driver, the data type should pass seamlessly into Excel as a serial date number. However, if the query casts the date to a string, or if data is exported via CSV, TSV, or legacy text pipelines, the client workstation must parse the string based on local machine settings.
  2. Ambiguous Parsing and the “Silent Flip”: In an MDY system (like standard US Windows defaults), Excel expects inputs formatted as MM/DD/YYYY. When encountering YYYY-MM-DD, Excel’s calculation engine tries to evaluate the string:
    • Dates like 2026-04-25 cannot be evaluated as MDY because 25 cannot be a month; Excel leaves them as raw text strings.
    • If a format like YY-MM-DD or custom text is parsed, dates where the day is 12 or less (e.g., 2026-05-04) may silently transpose May 4th into April 5th.
  3. Driver Transformation Gaps: When connecting through generic ODBC sources or flat-file exports, Power Query often injects an automatic Changed Type step that relies strictly on the workstation’s local regional settings. If the incoming format conflicts with the local culture, Power Query halts with DataFormat.Error: We couldn’t parse the input provided as a Date..
[SQL Server Date Column]
            │
            ├── Passed as Binary Date (OLE DB / ODBC) ──► Loads as True Serial Date
            │
            └── Passed as Text (CSV / Cast to VARCHAR)
                        │
                        ▼
            [Excel Parsing Engine (Windows MDY Settings)]
                        │
          ┌─────────────┴─────────────┐
          ▼                           ▼
[Day > 12: e.g., 2026-08-24]    [Day <= 12: e.g., 2026-05-04]
          │                           │
Unrecognized format:           Silent Flips:
Imported as uncalculable TEXT  Interpreted as May 4th or April 5th

Diagnostic Steps: Identifying the Date Format Break

Before applying a fix, identify whether the dates are stored as genuine numbers, corrupted text, or inverted records.

Step 1: Run the ISNUMBER Verification

Click an empty cell and enter the formula:
=ISNUMBER(A2)
(where A2 contains your imported date).

  • TRUE: Excel recognized the date and converted it into an internal serial number (e.g., 46135). Any formatting issue is purely visual and can be corrected by pressing Ctrl + 1 and choosing an MDY display format.
  • FALSE: Excel treats the date as a text string. Formulas like YEAR(), DATEDIF(), or mathematical addition will fail, and date-based functions will return errors like #VALUE! in NETWORKDAYS: Invalid Date formats.

Step 2: Audit Power Query Type Steps

If the failure happens during a Power Query refresh:

  1. Open the query editor (Data > Queries & Connections, right-click the query, and select Edit).
  2. Look at the Applied Steps panel on the right.
  3. Find any step named Changed Type and examine the code in the formula bar.
  4. If you see #table(...) or Table.TransformColumnTypes(Source, {{"OrderDate", type date}}), Power Query is trying to force the date using your machine’s default locale. If a row contains an incompatible string, the cell will display a red Error.

Primary Solutions

Power Query allows you to specify the exact cultural rules needed to interpret a date string without altering your Windows operating system settings:

  1. In the Power Query editor, select the date column.
  2. In the Home or Transform tab, click Data Type: [Any] or Data Type: [Text].
  3. Select Using Locale… from the bottom of the dropdown menu.
  4. In the dialog box:
    • Data Type: Select Date.
    • Locale: Select English (United States) or English (Canada) (or the specific source culture if using non-standard punctuation).
  5. Click OK.
let
    Source = Sql.Database("DatabaseServer", "InventoryDB"),
    RawData = Source{[Schema="dbo",Item="Shipments"]}[Data],
    // Explicitly parse the ISO 8601 string using a compatible culture
    FixedDateType = Table.TransformColumnTypes(RawData, {{"ShipDate", type date}}, "en-US")
in
    FixedDateType

By explicitly providing "en-US", the M engine applies ISO-compliant parsing logic directly to the column, bypassing local system discrepancies. If your pipeline involves European DMY formats instead of ISO patterns, refer to Handling “Locale” errors: Importing DMY dates into an MDY system..

Solution 2: Explicit Formatting in the SQL Extraction Query

If you pull data through a SQL statement or view, avoid string ambiguities by handling the conversion on the SQL Server host.

If you need a text output formatted strictly for MDY:

SELECT
    OrderID,
    -- Format 101 outputs US standard: MM/DD/YYYY
    CONVERT(VARCHAR(10), OrderDate, 101) AS OrderDateMDY,
    -- Format 120 outputs standard ISO: YYYY-MM-DD
    CONVERT(VARCHAR(10), OrderDate, 120) AS OrderDateISO
FROM Sales.Orders;

Alternatively, leave the column strongly typed as a database DATE instead of casting to VARCHAR:

SELECT
    OrderID,
    CAST(OrderDate AS DATE) AS OrderDateTyped
FROM Sales.Orders;

When delivered as a binary DATE over an OLE DB or ODBC provider, modern drivers translate the date directly into Excel’s serial date model, eliminating text parsing altogether. For broader database pipeline fixes, review SQL-to-Excel Data Mapping: Fixing Type Mismatches and Schema Breaks..

Solution 3: In-Sheet Fix Using “Text to Columns”

If the data is already sitting on an Excel worksheet as static, uncalculable text strings:

  1. Highlight the entire column of text dates.
  2. Go to the Data tab on the Ribbon and click Text to Columns.
  3. In Step 1 and Step 2 of the wizard, click Next (keep defaults).
  4. In Step 3 of 3, locate the Column data format section.
  5. Select the Date radio button, and in the adjacent dropdown, select YMD (matching the format of the text currently in the cells: Year, Month, Day).
  6. Click Finish.

Excel reads the four-digit year, two-digit month, and two-digit day in order, converts each entry into an internal serial date number, and displays it according to your computer’s local MDY system settings.

Verifying the Solution

To confirm that the dates are imported cleanly and ready for analysis:

  1. Format Check: Select the column, press Ctrl + 1, and apply a different date format (such as DD-mmm-YYYY). If the display changes from 05/14/2026 to 14-May-2026, Excel has stored the date as a true numeric value.
  2. Sort Check: Sort the column ascending. Text strings sort alphabetically (grouping all 2026-01 before 2026-02, but failing if formats vary), whereas real dates sort chronologically.
  3. Filter Inspection: Click the filter dropdown arrow on the column header. True Excel dates are automatically grouped into a hierarchical tree (Years > Months > Days). If the filter shows a flat list of text strings, the dates remain unparsed.

If your date columns contain integrated timezone data alongside timestamps, see Solving Date/Time offset errors: “The value is not a valid DateTimeZone.”.

Fixing “DataFormat.Error: We couldn’t parse the input as a Boolean.”

October 31, 2026

Handling “Null” values that break Power Pivot “Relationship” integrity.

October 31, 2026

Fixing “Truncated Data” errors when importing SQL VARCHAR(MAX) columns.

October 30, 2026