Forum Discussion
PivotTable regression in Version 2609. Duration fields formatted [h]:mm:ss no longer allow Sum.
Excel 365 Version 2609 (Build 16.0.20430.20032) appears to have changed PivotTable handling of duration fields formatted as mm:ss. New PivotTables default to Count of Total Hours and Value Field Settings only shows Count, Max, Min, and Count Numbers; Sum is missing. Existing PivotTables using Sum of Total Hours still calculate correctly, even after refresh, but Sum no longer appears in their settings. Numeric helper field =[@[Total Hours]]*24 restores Sum functionality. Appears to be a regression affecting duration fields.
I have verified that Excel is treating the data formatted as [h]:mm:ss as numeric and yet still will not allow sums as before.
1 Reply
Hello,
I’m seeing the same behavior on my 64-bit beta version (2611 Build 16.0.20601.20000)!
Workarounds found:
1- For the first PivotTable, first change the data format to "General," create the table while checking the "Add to Data Model" box, then switch the format back to "Time."
Subsequent PivotTables should then work.
2- Use Power Pivot.
