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

Your data is entered as Text, try converting to a numeric format

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.

@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.

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