How to Stop Power Pivot from Converting Durations to Dates
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.

- 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.
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.
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.
In Excel, navigate to the 'Data' tab and click on 'Get Data' or select your existing query to open the Power Query Editor.
Locate the column containing your duration values. Click on the data type icon on the left side of the column header.
From the dropdown menu, select 'Duration' (or 'Time' if appropriate). If errors appear, check your source data for invalid text characters.
Click 'Close & Load To...' from the Home tab, check 'Add this data to the Data Model', and verify the formatting in Power Pivot.

Convert Time Values to Numeric Decimal Durations
Convert duration values into decimal numbers representing days or hours. This ensures Power Pivot treats them as standard numeric values rather than dates.
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. Download and Install: Visit the official WPS website to download and install WPS Office for free.
- 2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx data files with full compatibility.
- 3. Create Pivot Tables: Go to the Insert tab and select PivotTable to easily summarize and analyze your data without complex data modeling.

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.




