Subtotal of Sum of Hours and Not Count

JFK_Lives

New Member
Joined
Aug 24, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Excel Version = 365
OS = Windows

Hi Everybody,

Still New to the whole Power Pivot thing so please bare with me as I will have a lot of Obvious Questions that might seem stupid.

Summary:

I have Engineers that work on Work Orders, Each Work Order has a Start Date, Start Time and a End Date and End Time, Multiple Engineers can work on one Work Order. All of this Information is Pulled into a Pivot Table and Grouped Per Engineer. Required is a Subtotal that Sum of All of the Hours worked by That Engineer on All Work Orders, At The Moment I can Only Get a Count Instead of a Sum.

Data Table:

This is how the Data Table looks like, with Formatting and Custom Column added to workout the Total Hours per Start and End Time.
TimeTable.jpg


Start Time Column
Data Type: Date
Format: hh:mm:ss (13:30:55)
Formula: None

End Time Column
Data Type: Date
Format: hh:mm:ss (13:30:55)
Formula: None

Hours (hh:mm:ss) Custom Column
Data Type: Date
Format: hh:mm:ss (13:30:55)
Formula: = End Time - Start Time

Pivot Table:
TimePivot.jpg


After Creating the Pivot Table, All the Columns look fine.
But as Soon as I move the Hours (hh:mm:ss) Field to the Values on the Right-hand Side, I either get a Count of or Nothing.
So for Asset Number MILT-PC701M which has Hours of 00:05:00/00:10:00/00:10:00/00:10:00/ which should be a Subtotal of 00:35:00 Minutes I get either 1 or 00:00:00

TimePivot01.jpg


What am I missing here, is the Data Formatting Off, Need Another Custom Column or Formula?

Any Help or Suggestions will be Greatly Appreciated.

Thanks
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
I suspect you have already tried this, but is it as simple as clicking on the "Count of Hours" (the area you have circled in red). and selecting "Sum" under the "Summarize value field by" setting?

If this isn't an option, it's possible that your hourly data is incorrectly formatted as text - you may want to check that as well.

Screenshot 2022-09-08 155031.png
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,173
Members
452,615
Latest member
bogeys2birdies

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
Back
Top