calculated field in excel pivot

Hi Excel Gurus,

I need help to solve this scenario.

My Raw data has columns MS2010

Name Lead Break Lunch BioBreak
Abhishek Amit Pankaj 0:00:00 0:00:00 0:00:00
Kreetika Amit Pankaj 0:19:31 0:33:06 0:00:00
tiwari Amit Pankaj 0:00:00 0:00:00 0:13:28
Kreetika Amit Pankaj 0:00:00 0:00:00 0:13:53
Sharma Amit Pankaj 0:04:30 0:33:46 0:00:00
tiwari Amit Pankaj 0:00:00 0:29:22 0:05:26
subham Amit Pankaj 0:00:00 0:30:25 0:00:00
sumit Amit Pankaj 0:19:09 0:33:44 0:08:59

I made calculated columns - all break which is a sum of all three breaks and worked properly
now i made a another calculated column with elapsed time =IF((HOUR('All breaks' )-1)<0,0,SUM(('All breaks' )-TIME(1,0,0)))

I get the right elapsed time when i look with each names but the subtotal is giving incorrect answer ,

can anyone help me fixing this , much appreciate your response.


Sam

Simply put, you can't have desired value in SUBTOTAL/TOTAL column using calculated field in standard pivot table.

Since you are subtracting constant, Time(1,0,0).

In calculated field, SUM is performed on column(s) before any other calculations are applied. Hence, total will evaluate to SUM('All breaks') - Time(1,0,0).

You will need additional column in source table. Or PowerPivot (only available for Excel 2010 or later, and depends on SKU).

You wouldn't need manual work, you could add the columns for ALL BREAKS and ELAPSED TIME using the following formulas respectively:
=IF(COUNTIF(A$2:A2,A2)=1,SUMPRODUCT((A$2:A$9=A2)*(C$2:E$9)),"")
=IFERROR(IF(F2<1/24,0,F2-1/24),"")
Your Pivot Table will then show a Grand Total for the Elapsed Time of 8:22
Let us know if you have any questions.