asked on
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
EE_pivot_not_calculating_time.xlsx
Microsoft ExcelMicrosoft Office
Last Comment
agwalsh
Your data is entered as Text, try converting to a numeric format
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
@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...
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.
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.
ASKER
Thanks to both of you - turns out it was a data thing that they have to sort out on their end :-)