Log In
Register
Home
Dashboards
Power Query
Power Pivot
Power BI
February 6, 2022
Offline
Hi, i would appreciate any advice on how to sum time values in a pivot table. These time values represent the duration that a student has spent reading books.
Data table name = "DataTable" on the worksheet labelled "ReadingLog"
- The values range from 00:00:00 through to values in excess of 24 hours and they are formatted HH:MM:SS
Pivot table name = "pivotHours" on the worksheet labelled "Hours"
- When i bring the Time Spent field into the values section, all of them are 00:00:00
I've tried many different time formats but just seem to get any to work.
Thank you in advance.
James
See Answer
Trusted Members
Moderators
January 31, 2022
Offline
Would be helpful if you could upload an example file. Otherwise, make sure that you custom format the cells with time spent on reading as [hh]:mm:ss
The square brackets surrounding the hh turn the values from a regular Time to a Duration and allow you to go beyond 24 hours.
Dashboards
Power Query
Power Pivot
Power BI
February 6, 2022
Offline
Hi Riny, my apologies - see attached (kutubee.xlsx).
fyi I'm using MS Excel 365 on Windows
I've applied the formatting and then refreshed the data but no joy 
Trusted Members
Moderators
January 31, 2022
Offline
The "time" values in ReadingLog column F were in fact texts. I used Text-to-columns to transform them to real time values and refreshed the pivot table. It seems to work. See attached.
Answers Post
1 Guest(s)
