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!
1 Answer
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))
