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
EE_pivot_not_calculating_time.xlsx
LVL 1
agwalshAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Your data is entered as Text, try converting to a numeric format
NorieAnalyst 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
agwalshAuthor 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.
agwalshAuthor Commented:
Thanks to both of you - turns out it was a data thing that they have to sort out on their end :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.