How to summarise time values in a Pivot table?

vergab

New Member
Joined
Jun 21, 2016
Messages
22
Hi there,
I try to create a Pivot table from these data:

Area


TTSiteWork timeMonth
AINC000000177407168725:08:512
BINC000000183547277124:24:383
BINC000000132330107519:31:043
CINC000000231001270918:31:275
AINC000000254227407927:04:107
AINC000000255227570731:48:567
CINC000000259337273221:58:037
C INC000000276814270922:55:138

<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?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

MattVizor

New Member
Joined
Sep 14, 2016
Messages
21
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

New Member
Joined
Jun 21, 2016
Messages
22
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

New Member
Joined
Jun 21, 2016
Messages
22
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

New Member
Joined
Sep 14, 2016
Messages
21

ADVERTISEMENT

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 LabelsSum of Work time
A84:01:57
B43:55:42
C63:24:43
Grand Total191:22:22

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

vergab

New Member
Joined
Jun 21, 2016
Messages
22
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

New Member
Joined
Sep 14, 2016
Messages
21

ADVERTISEMENT

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

New Member
Joined
Jun 21, 2016
Messages
22
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

New Member
Joined
Sep 14, 2016
Messages
21
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

New Member
Joined
Jun 21, 2016
Messages
22
Re: How to summerise time values in a Pivot table?

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

Watch MrExcel Video

Forum statistics

Threads
1,129,430
Messages
5,636,232
Members
416,909
Latest member
hayleyg123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top