We help IT Professionals succeed at work.

Pivot Table not adding up field with time duration

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
Watch Question

Roy CoxGroup Finance Manager

Your data is entered as Text, try converting to a numeric format
Analyst Assistant
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.


@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

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.


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