Power Query Problems

How to Stop Power Pivot from Converting Durations to Dates

Rana GarciaRana Garcia Oct 9, 2026 871 views

Question details

The user needs a way to prevent duration values from being automatically reformatted and interpreted as dates and times when importing data into the Excel data model.

How to Keep Duration Values from Becoming Dates in Power Pivot
Product
Microsoft Excel
Device & OS
not provided
Scenario
Importing and modeling data that contains time duration values using Power Query and loading it into the Power Pivot data model.
Observed behavior
Power Pivot incorrectly interprets duration values as dates and times, and attempting to convert the column to a Duration type in Power Query produces errors.
Before you start

Verify that your source data consistently uses valid time formats and ensure your Excel Power Query and Power Pivot add-ins are enabled and up to date.

Solution 1Recommended

Assign the Correct Data Type in Power Query

Pre-format your data columns strictly in Power Query before loading them into the Power Pivot data model to prevent automatic date conversion.

Power Pivot relies heavily on the data types defined during the import process. If a column is left as 'Any' or incorrectly guessed, the data model may apply a default Date/Time format. Explicitly defining the data type as Duration or Time in Power Query resolves this.

1
Open Power Query Editor

In Excel, navigate to the 'Data' tab and click on 'Get Data' or select your existing query to open the Power Query Editor.

2
Select the Target Column

Locate the column containing your duration values. Click on the data type icon on the left side of the column header.

3
Change Data Type to Duration

From the dropdown menu, select 'Duration' (or 'Time' if appropriate). If errors appear, check your source data for invalid text characters.

4
Load to Data Model

Click 'Close & Load To...' from the Home tab, check 'Add this data to the Data Model', and verify the formatting in Power Pivot.

Assign the Correct Data Type in Power Query
Handling Duration Errors: If you receive errors when converting to Duration, your source data might contain mixed formats. Try replacing text values or converting the column to text, then to a standard time format, before applying the Duration type.
Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Data Analysis

While advanced Power Pivot data modeling is native to Microsoft Excel, WPS Office provides a free, highly compatible alternative for everyday spreadsheet tasks, pivot tables, and data visualization. Enjoy a familiar interface without the hefty subscription fees.

  1. 1. Download and Install: Visit the official WPS website to download and install WPS Office for free.
  2. 2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx data files with full compatibility.
  3. 3. Create Pivot Tables: Go to the Insert tab and select PivotTable to easily summarize and analyze your data without complex data modeling.
Free, lightweight, and fast-loadingHighly compatible with Microsoft Excel (.xlsx) formatsBuilt-in Pivot Tables for powerful data summarizationFamiliar user interface with zero learning curve
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel Power Pivot convert my durations to dates?

Excel stores times and dates as serial numbers (where 1 equals one full day). If a cell contains a time value without strict formatting instructions, the Power Pivot data model defaults to reading that serial number as a standard Date/Time format.

How do I fix 'Data Format Error' when converting a column to Duration in Power Query?

This error typically occurs if the source column contains blank cells, text strings, or mixed time formats (e.g., '2 hrs' instead of '02:00'). Clean the column by filtering out invalid data or replacing text strings before applying the Duration data type.

Can I format decimal numbers as durations in Power Pivot?

Yes. If you load your durations as decimal numbers, you can use DAX functions like FORMAT() within your Power Pivot measures to convert the numeric values back into readable 'HH:MM:SS' text strings for reporting.