How to Import Excel Hours Correctly into Power Pivot
Question details
Users need a way to properly display and calculate hour durations imported from Excel into Power Pivot.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing Excel columns formatted as [h]:mm into the Power Pivot data model.
- Observed behavior
- The imported time durations do not display correctly because Power Pivot lacks a native Duration data type.
Ensure your source Excel columns contain valid time data before attempting to load them into the Power Pivot data model.
Calculate Duration as a Decimal Number using DAX
Since Power Pivot does not support the Duration data type, convert the time difference into a Decimal Number using a DAX formula.
Power Query supports the Duration data type natively, but loading this directly into Power Pivot causes compatibility and formatting problems.
To bypass this limitation, you can calculate the duration directly within Power Pivot as a decimal value, which is fully supported.
Navigate to the Power Pivot tab in your Excel ribbon and click on 'Manage' to open the Power Pivot window.
Click on an empty column in your data model and enter the DAX formula: [DurationPP] = DATEDIFF([Start], [End], SECOND) / 86400. Alternatively, you can simply use: [DurationPP] = [End] - [Start].
Select the newly created column. Under the Formatting section on the Home tab, change the Data Type from its default to 'Decimal Number'.
If you need the result to visually display as hours and minutes, wrap your calculation in a FORMAT expression to output the result as a text string.
Need Advanced Data Analysis? Try WPS Office
If you are struggling with complex add-ins like Power Pivot, WPS Office offers robust native Pivot Tables, comprehensive data analysis tools, and excellent compatibility with Microsoft Excel files—completely free.
- 1. Download and Install: Get WPS Office from the official website and install it on your device for free.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your existing Excel (.xlsx) workbook directly.
- 3. Insert a Pivot Table: Highlight your data, navigate to the Insert tab, and click PivotTable to begin analyzing your records effortlessly.
Frequently Asked Questions
Why is the Duration data type missing in Power Pivot?
Power Pivot's underlying data model does not include a built-in Duration data type. It only supports primary types such as Text, Date, and Decimal Number.
Can I use Power Query instead to handle durations?
Yes, Power Query natively supports Duration data types. However, if you plan to load that data from Power Query into the Power Pivot data model, you will still need to convert it to a Decimal Number to prevent import issues.
How do I format the decimal result back to [h]:mm?
To display the calculated decimal number as a traditional time format in Power Pivot, use a DAX FORMAT function. Keep in mind this will change the column's data type to Text, meaning you cannot perform further math on it without converting it back.
