Pivot Table Calculated Fields

[Slim Slender]

Here is a sample of my data:

Date Employee Hours
6/1/2009 Steve 8.00
6/1/2009 Jack 5.50
6/1/2009 Steve 1.50
6/1/2009 Jack 1.50
6/1/2009 Mike 8.50
6/1/2009 Jack 2.50
6/2/2009 Steve 8.00
6/2/2009 Jack 7.00
6/2/2009 Mike 4.50
6/2/2009 Steve 1.50
6/2/2009 Jack 1.00
6/2/2009 Mike 4.50
6/3/2009 Steve 8.00
6/3/2009 Mike 8.00
6/3/2009 Jack 6.50
6/3/2009 Steve 1.50
6/3/2009 Mike 1.00
6/3/2009 Jack 3.00
6/4/2009 Steve 8.50
6/4/2009 Mike 9.50
6/4/2009 Jack 8.00
6/4/2009 Steve 1.50
6/4/2009 Jack 1.50
6/5/2009 Steve 8.00
6/5/2009 Jack 8.00
6/5/2009 Mike 6.50
6/5/2009 Mike 5.00

I created two Calculated Fields for a Pivot Table:
Calculated Field
Solve Order Field Formula
1 Regular Time =IF(Hours >40,40,Hours)
2 Overtime =IF(Hours >40,Hours-40,0)

Here is the Pivot Table:
Employee Data Total
Jack Sum of Regular Time 40.00
Sum of Overtime 4.50
Sum of Hours 44.50
Mike Sum of Regular Time 40.00
Sum of Overtime 7.50
Sum of Hours 47.50
Steve Sum of Regular Time 40.00
Sum of Overtime 6.50
Sum of Hours 46.50
Total Sum of Regular Time 40.00
Total Sum of Overtime 98.50
Total Sum of Hours 138.50

All figures for the individuals are correct and the Total Sum of Hours
at the bottom is correct, but the Total Sum of Regular Time s/b 120.00
and the Total Sum of Overtime s/b 18.50. What am I doing wrong?

 

[Leung]

Hi

can i know how to created two Calculated Fields in the pivot table?

so you add to the pivot table directly rather than adding 2 columne ?

 

[Slim Slender]

To create a Calculated Field in Excel 2003: with the cursor in the
pivot table, click on the Pivot Table drop down list on the Pivot
Table tool bar. Then click on Formulas, then Calculated Field.

 

[Herbert Seidenberg]

Excel 2007 PivotTable
Use Calculated Item, not Calculated Field,
only available in PT, type "Consolidate".
Applies to Excel 2003 too.
http://www.mediafire.com/file/n2iw42lmg0j/12_28_09.xlsx

 

[Slim Slender]

Excel 2007 PivotTable
UseCalculatedItem, notCalculatedField,
only available in PT, type "Consolidate".
Applies to Excel 2003 too.http://www.mediafire.com/file/n2iw42lmg0j/12_28_09.xlsx

Prove it!