Solving Date/Time offset errors: “The value is not a valid DateTimeZone.”

When pulling time-stamped transactional records from relational databases into Excel, imports frequently stop on the error: DataFormat.Error: The value is not a valid DateTimeZone. This error indicates that Power Query or the underlying connection driver attempted to evaluate a date-time string containing timezone offset information into a native date type, but the text format violates the strict ISO 8601 formatting required by the Power Query M engine.

In SQL Server, the DATETIMEOFFSET data type preserves the UTC offset (such as 2026-10-22 14:30:00.0000000 +02:00). While Power Query contains a specialized DateTimeZone data type, standard Excel worksheet grids do not support timezones; cells can only store dates as serial numbers. When conversion steps fail along this pipeline, the import breaks.

Quick Fix: Resolve DateTimeZone Validation Breaks

Convert the DATETIMEOFFSET column to standard UTC or local time before passing it to the grid. In Power Query, select the column, navigate to Transform > Date & Time Column > Time, and select Timezone > To Local Time followed by changing the data type to Date/Time. In SQL, use CONVERT(DATETIME2, SWITCHOFFSET(YourColumn, '+00:00')).

---

What Causes “The value is not a valid DateTimeZone”

Power Query validates DateTimeZone strings against strict RFC 3339 and ISO 8601 standards [3]. If any character in the incoming data stream deviates from expected formatting, the parsing engine throws DataFormat.Error.

The most common causes include:

1. Non-Standard Timezone Separators: Power Query expects timezone offsets to follow +HH:MM or HH:MM. If an external driver or text export delivers offsets without a colon (such as +0200 instead of +02:00), Power Query fails to recognize the timezone offset.
2. Invalid Space Delimiters: Standard ISO format requires a T or a single space between the date and time components. Double spaces, custom punctuation, or trailing null bytes break the parser.
3. Worksheet Grid Rejection: The Excel worksheet grid does not have a DateTimeZone data type. If a Power Query script successfully parses a DateTimeZone object but attempts to load it directly into a standard sheet table without stripping the offset, Excel attempts an internal conversion that can trigger this error.
4. Mixed Nulls or Blank Strings: When a column contains both valid date strings and blank space characters (" "), Power Query attempts to evaluate the blank string as an offset timestamp, throwing a format failure on that specific row.

For related parsing issues when working with standard date representations, see DataFormat.Error: We couldn’t parse the input provided as a Date..

---

Step-by-Step Diagnostic Routine

Follow these steps to trace where the formatting failure occurs:

[SQL Server DATETIMEOFFSET]
             │
             ▼
[Driver / Direct Query] ──(Custom format)──► Check ISO 8601 offset (+HH:MM)
             │
             ▼
[Power Query Source Step]
   ├── Type = datetimezone ──► Must strip zone before loading to worksheet
   └── Type = text ──────────► Parse via DateTimeZone.FromText or DateTime.From
             │
             ▼
[Excel Worksheet Destination] (Serial Date Number Only: No native offset support)

1. View the Raw Text in Power Query

Open Power Query. Go to the Applied Steps pane on the right side and click on the Source or Navigation step, before any automatic Changed Type steps.

Look at the raw string representation of the problematic column:

• Valid format: 2026-10-22 14:30:00 +02:00 or 2026-10-22T14:30:00+02:00
• Invalid format: 2026-10-22 14:30:00 +0200 (missing colon in offset)
• Invalid format: 2026-10-22 14:30:00.000 (declared as DateTimeZone but missing offset entirely)

2. Identify the Failing Row

If your dataset contains thousands of rows and the error only appears on refresh:

1. In Power Query, select Home > Keep Rows > Keep Errors.
2. This isolates only the rows causing DataFormat.Error.
3. Inspect the isolated records to see if the error is caused by corrupted text, non-standard timezone stamps, or blank values.
4. Delete the “Keep Errors” step from your Applied Steps pane once inspected.

---

Primary Fixes

Method 1: Convert Offset at the SQL Server Level

Converting timestamps inside the SQL database engine eliminates driver translation problems entirely. It also offloads computation from Excel to the database server.

Use SWITCHOFFSET to normalize the timestamps to UTC, then convert to DATETIME2:

SELECT
    RecordID,
    -- Normalizes offset to UTC and casts to standard datetime
    CONVERT(DATETIME2, SWITCHOFFSET(OrderDateTimeOffset, '+00:00')) AS OrderDateTimeUTC,
    -- Alternatively, convert directly to local server time
    CONVERT(DATETIME2, OrderDateTimeOffset) AS OrderDateTimeLocal
FROM Sales.Orders;

Because DATETIME2 maps directly to Excel’s native Date/Time structure, the data loads cleanly without triggering timezone parsing routines.

Method 2: Strip or Normalize the Offset in Power Query M-Code

If you cannot edit the database query, handle the conversion within Power Query.

If the column is currently recognized as type datetimezone, use DateTimeZone.ToLocal or DateTimeZone.RemoveZone:

let
    Source = Sql.Database("DatabaseServer", "OperationsDB"),
    DataTable = Source{[Schema="dbo",Item="ActivityLog"]}[Data],

    // Step 1: Ensure column is typed as datetimezone
    SetZoneType = Table.TransformColumnTypes(DataTable, {{"LoggedAt", type datetimezone}}),

    // Step 2: Convert to local workstation time
    ConvertToLocal = Table.TransformColumns(SetZoneType, {{"LoggedAt", DateTimeZone.ToLocal, type datetimezone}}),

    // Step 3: Strip the timezone component so the Excel grid can accept it
    StripZone = Table.TransformColumnTypes(ConvertToLocal, {{"LoggedAt", type datetime}})
in
    StripZone

If the error occurs because the incoming text is malformed (e.g., +0500 instead of +05:00), insert the colon using text transformation before parsing:

let
    FixOffsetString = Table.TransformColumns(SourceTable, {{"RawDateText", each
        if _ <> null and Text.Length(_) > 5 and (Text.EndsWith(_, "00") or Text.EndsWith(_, "30")) and Text.Contains(_, "+")
        then Text.Start(_, Text.Length(_) - 2) & ":" & Text.End(_, 2)
        else _, type text}}),
    ParseDate = Table.TransformColumnTypes(FixOffsetString, {{"RawDateText", type datetimezone}})
in
    ParseDate

If you encounter regional formatting conflicts where dates switch between MDY and DMY conventions along with offsets, review Troubleshooting “Locale” errors: Importing SQL YYYY-MM-DD into MDY Excel..

---

Verifying the Correction

1. Verify M-Code Data Types: Inspect the icon next to the column header in Power Query. It must display a small Calendar and Clock icon (representing DateTime), not a Calendar, Clock, and Globe icon (which represents DateTimeZone).
2. Review Worksheet Load: Click Close & Load. Ensure all rows load without throwing warnings in the Queries & Connections sidebar.
3. Verify Formula Usability: In an empty cell, write an arithmetic formula against the loaded date (such as =A2 + 1). If Excel returns a valid date 24 hours later rather than a #VALUE! error, the date is successfully stored as an Excel serial date.

For dedicated troubleshooting on pure M-code timezone expression breaks, see Fixing “DataFormat.Error: The value is not a valid DateTimeZone.”. When building interview schedules, shift rotations, or attendance models across multiple offices, consult Timezone Conversion: Handling #VALUE! when calculating interview times across UTC offsets..