How to summarise time values in a Pivot table?

[vergab]

Hi there,
I try to create a Pivot table from these data:

[TABLE="width: 304"]
<tbody>[TR]
[TD]Area


[/TD]
[TD]TT[/TD]
[TD]Site[/TD]
[TD]Work time[/TD]
[TD]Month[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]INC000000177407[/TD]
[TD="align: right"]1687[/TD]
[TD="align: right"]25:08:51[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]INC000000183547[/TD]
[TD="align: right"]2771[/TD]
[TD="align: right"]24:24:38[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]INC000000132330[/TD]
[TD="align: right"]1075[/TD]
[TD="align: right"]19:31:04[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]INC000000231001[/TD]
[TD="align: right"]2709[/TD]
[TD="align: right"]18:31:27[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]INC000000254227[/TD]
[TD="align: right"]4079[/TD]
[TD="align: right"]27:04:10[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]INC000000255227[/TD]
[TD="align: right"]5707[/TD]
[TD="align: right"]31:48:56[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]INC000000259337[/TD]
[TD="align: right"]2732[/TD]
[TD="align: right"]21:58:03[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]C [/TD]
[TD]INC000000276814[/TD]
[TD="align: right"]2709[/TD]
[TD="align: right"]22:55:13[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

I would like to summerise the working time values like this:
Area A - 84:01:57, and so on.
But it shows the result with the quantity of Area A (3). How should I do it?

 

[MattVizor]

Re: How to summerise time values in a Pivot table?

Hi
You will need to change the format of your time field to Custom [h]:mm:ss in both your initial table and the pivot table. Then change the value from a count to a sum

 

[vergab]

Re: How to summerise time values in a Pivot table?

Hi,
The Custom [h]:mm:ss shows only the hours, but I need the mm:ss as well.

 

[vergab]

Re: How to summerise time values in a Pivot table?

I see the hh:mm:ss format now, but it can not summerise the values. It counts only the quantity and shows it in this format.

 

[MattVizor]

Re: How to summerise time values in a Pivot table?

I have used the [h]:mm:ss and get this, so not sure why you aren't. Have you changed the values from count to sum?

[TABLE="width: 213"]
<tbody>[TR]
[TD]Row Labels[/TD]
[TD]Sum of Work time[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]84:01:57[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]43:55:42[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]63:24:43[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]191:22:22[/TD]
[/TR]
</tbody><colgroup><col><col></colgroup>[/TABLE]

 

[vergab]

Re: How to summerise time values in a Pivot table?

I changed the format so I can see this "25:08:51" in the cell. But when I click on this cell I can see in the formula bar this format: "1900.01.01 1:08:51". I think it is the cause of problem so that is why appears the error message "You can not summarize the date format".

 

[MattVizor]

Re: How to summerise time values in a Pivot table?

It isn't a date format. the [h]:mm:ss is the way to cumulatively add times. You will need to change the format in your pivot table too.

 

[vergab]

Re: How to summerise time values in a Pivot table?

Yes, I know I changed the format in the excel table and in the Pivot as well but it still in this kind of date format. What should be wrong with it? Are there any additional feature in excel or Pivot?

 

[MattVizor]

Re: How to summerise time values in a Pivot table?

Not that I am aware of - it seems very strange. What happens if you do a sum of the time values in your original table?

 

[vergab]

Re: How to summerise time values in a Pivot table?

It works properly in the original excel table with sumif funktion.