How to summarise time values in a Pivot table?

[vergab]

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

Area	TT	Site	Work time	Month
A	INC000000177407	1687	25:08:51	2
B	INC000000183547	2771	24:24:38	3
B	INC000000132330	1075	19:31:04	3
C	INC000000231001	2709	18:31:27	5
A	INC000000254227	4079	27:04:10	7
A	INC000000255227	5707	31:48:56	7
C	INC000000259337	2732	21:58:03	7
C	INC000000276814	2709	22:55:13	8

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

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?

Row Labels	Sum of Work time
A	84:01:57
B	43:55:42
C	63:24:43
Grand Total	191:22:22

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[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.