Excel pivot table: grand total of hours

Question

I have created a pivot table for the following data in excel:

But as you can see, the grand total is not being calculated correctly. What should I do to fix it? My second question is, how could I drop the duration column and use a formula in pivot table for that?

Answer 1

I would guess you just need to format that cell (H5) as a time that can exceed 24 hours: http://support.microsoft.com/kb/266691 so you just need square brackets around the h in your custom formatting string.

For your second question, you need a calculated field. In 2010, click on the pivot table, go to the options tab in the ribbon, in the calculations group choose Fiels, Items & Set and then choose calculated field. I guess then you just need End Time - Start Time

Answer 2

Use formatting as [h]:mm to the time field (Sum of Duration).

It worked for me, could for you as well.