Pivot Table not adding up field with time duration

agwalsh
agwalsh used Ask the Experts™
on
I've got a pivot table with time duration data - time completed - time. However when I go to run a pivot to summarize by Cleaning it defaults to Count and tells me that Sum is not an allowed Data type for date...what am I missing? Thanks
EE_pivot_not_calculating_time.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Roy CoxGroup Finance Manager

Commented:
Your data is entered as Text, try converting to a numeric format
Analyst Assistant
Commented:
When I try and change the field to Sum I get an error message saying Sum is not a supported calculation for Text data types, Date isn't mentioned.

However if I create a new pivot table I am able to set Time Done to Sum.

How did you create the pivot table that's giving you trouble?

To create the pivot table that worked I placed the cursor in the body of the table on 'Cleaned Up', went to Insert>Pivot table..., selected New Worksheet, added Cleaning as a row item, Time Done as a value item and then changed the field settings for Time Done to Sum.

See the attached.

PS I did get strange results but I think that's because of the date, for example in some columns you have integer values rather than times.
EE_pivot_not_calculating_time.xlsx

Author

Commented:
@Ray Cox - which bit is entered as text? I formatted the three time columns as [hh]:mm.  @Norie, I created the pivot table by formatting data as table and then Design | Summarize as Pivot Table. Had a look at the one you sent over - it does do the sum but doesn't give the actual hours and when I try to format it to [hh]:mm I just get hashtags...
NorieAnalyst Assistant

Commented:
What do you mean it doesn't give the 'actual hours'?

Have you checked the data?

For example in row 46 you have 1 in the Time column and 2 in the Time Completed column, those values represent 1 day and 2 days respectively and that's why you get 24:00 in the Time done column for that row.

Similarly in row 52 you have 8 in the Time column and 08:35 in the Time Completed column.

In that row the value Time done column appears as ####### due to the formatting.

If you change the formatting to General you'll see that the actual value in Time done is -7.642361111.

Also, the formula in the Time done column doesn't seem to have been copied right down.

Author

Commented:
Thanks to both of you - turns out it was a data thing that they have to sort out on their end :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial