Pivot: Min/Max of time - How to calculate amount of time between those two values?

[Darkzler]

Hi all,

I got this setup but can't figure out how to get the total amount of time from column C to column D.

[TABLE="width: 414"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Count of Shipment[/TD]
[TD]Min of Tid[/TD]
[TD]Max of Tid[/TD]
[/TR]
[TR]
[TD]Person A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Trp2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10:48:00[/TD]
[TD="align: right"]10:48:00[/TD]
[/TR]
[TR]
[TD]Trp3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]12:29:00[/TD]
[TD="align: right"]16:52:00[/TD]
[/TR]
[TR]
[TD]Trp4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]09:14:00[/TD]
[TD="align: right"]16:45:00[/TD]
[/TR]
[TR]
[TD]Trp1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]09:24:00[/TD]
[TD="align: right"]16:44:00[/TD]
[/TR]
[TR]
[TD]Person B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Trp5[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]08:46:00[/TD]
[TD="align: right"]11:42:00[/TD]
[/TR]
[TR]
[TD]Trp6[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]09:22:00[/TD]
[TD="align: right"]13:32:00[/TD]
[/TR]
[TR]
[TD]Trp7[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]09:23:00[/TD]
[TD="align: right"]12:04:00
[/TD]
[/TR]
</tbody>[/TABLE]


When I try to enter a calculated item it just says: "Multiple data fields of the same field are not supported when a PivotTable report has calculated items."

Any suggestions on how I get the difference between Max of Tid and Min of Tid?

 

[oldbrewer]

a helper column outside of the pivot with if (say) if(or(d6="",d6="Grand Total"),"",I6-h6)

 

[Darkzler]

Hi Oldbrewer,

That is my current solution but this pivot table will grow for each day that goes and to manually adjust this is a step I really try to avoid.

 

[oldbrewer]

the helper column can be 10000 rows deep - it will only pick up your min and max values - that is the point of error trapping out blanks and grand totals