Grand Total Not Displaying Correctly for Pivot Table's Calculated Field

Question

I have the calculated field Break Lateness to determine how late an agent is from Break 1 by using the following formula:

IF( 'Break 1' > TIME(0,15,0) , 'Break 1' - TIME(0,15,0), TIME(0,0,0) )

The formula seems to work fine on cell range: C3:C12. But the Grand total in C2 and C13 says: 1:57:43 but I want it to show a regular SUM(C2:C13), that is, I want it to say: 0:00:56.

Here's a picture of how the pivot table looks like:

The format I'm using is: Time: 37:50:55

I tried changing the time format but it won't work.

I checked this post and other ones but was unable to determine the reason or find a fix.

Here's a link to the actual workbook if you want to open it.

If you need ANY more info don't hesitate to let me know, I'll reply ASAP!

Thank you!

Solution

I tested and found the Sum of Break Lateness also use the Calculated fields to get the Grand Total, this is a known issue in Excel, you can refer to this link:

Calculated field returns incorrect grand total in Excel

As a workaround use the formula in data source first and then remove the problem PivotTable and create the PivotTable:

Formula: =IFERROR(IF([@[Break 1]]>=TIME(0,15,0),[@[Break 1]]-TIME(0,15,0),TIME(0,0,0)),TIME(0,0,0))

Comments

[10110]

thanks a lot, I really thought I could do something to avoid what you suggested but with the link you provided I'm at peace that it's an actual issue with Excel. Thank you! Will do it your way !.