How to Fix Incorrect Excel PivotTable Totals for Time ([h]:mm) Values
Question details
The user needs to resolve an issue where Excel PivotTable subtotals and totals for time values do not match the expected sum shown in the status bar.
![How to Fix Incorrect Excel PivotTable Totals for Time ([h]:mm) Values](https://res-academy.cache.wpscdn.com/upload/seo-engine-admin/2026-11-02/5408c561de9d1ec2b8de0ead37f60bc1.png)
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Aggregating allocated minutes converted via the TIME function inside a PivotTable using a custom duration format.
- Observed behavior
- The PivotTable subtotals and grand totals display incorrect amounts compared to the actual sum of the source data, despite the values being displayed using the [h]:mm format.
Ensure your source data contains actual Excel numerical time values (representing fractions of a day) rather than text strings that merely look like times.
Apply [h]:mm Format via Value Field Settings
The most common reason for incorrect PivotTable time totals is applying the cell format to the worksheet display rather than the underlying PivotTable value field.
Formatting the raw source data cells or manually changing the cell format in the PivotTable grid often fails to apply the format to the PivotTable's internal aggregation logic. To ensure totals and subtotals display correctly for durations exceeding 24 hours, the format must be applied directly to the Value Field Settings.
Click on any cell within the values area of your PivotTable that contains the time data.
Right-click the selected cell and choose 'Value Field Settings' from the context menu.
In the 'Summarize Values By' tab, ensure the calculation type is set to 'Sum' rather than 'Count'.
Click the 'Number Format' button located at the bottom left of the dialog box.
Select 'Custom' from the Category list, type '[h]:mm' in the Type field, and click 'OK' on both dialog boxes to apply the changes.
![Apply [h]:mm Format via Value Field Settings](https://res-academy.cache.wpscdn.com/upload/seo-engine-admin/2026-11-02/357b96372632f2f2bb39cde57bd32fd1.png)
Verify Source Data Time Conversion
Ensure the source column calculates time properly so the PivotTable has valid numerical data to aggregate.
Create and Format Pivot Tables Effortlessly with WPS Office
WPS Spreadsheet provides robust PivotTable functionality with full support for custom time formats like [h]:mm. You can easily aggregate, summarize, and format complex duration data with an intuitive interface.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the time data.
- 2. Insert a PivotTable: Go to the 'Insert' tab and click 'PivotTable' to create your data summary.
- 3. Configure Value Field Settings: Drag your time field into the 'Values' area, right-click any value cell, and select 'Value Field Settings'.
- 4. Apply [h]:mm Format: Ensure the field is set to 'Sum', click 'Number Format', and apply the custom format '[h]:mm'.
Frequently Asked Questions
Why does my Excel time sum reset after 24 hours?
Standard time formats like 'hh:mm' represent a specific time of day on a clock, which resets every 24 hours. To display total elapsed time or durations over 24 hours, you must use square brackets around the hour in the custom formatting, like '[h]:mm'.
Can I format the source data cells instead of using Value Field Settings?
While formatting the source data helps you read the raw numbers, PivotTables often rely on their own internal formatting rules when generated. It is highly recommended to apply formats directly through the PivotTable's Value Field Settings to ensure subtotals and grand totals display the correct format natively.
What happens if my time values are stored as text?
PivotTables cannot mathematically sum text strings. If your times are stored as text, the PivotTable will default to the 'Count' aggregation instead of 'Sum'. You must convert the text to numerical time values in the source data using functions like TIMEVALUE before refreshing the PivotTable.
