Laura has attended:
Excel Advanced course
In a pivot table,
I want it to calculate the total hours worked in a pivot table. The data is pulling through in time format 06:15 for example, however when I change the filter to show the count as a total sum, it does not calculate the total hours worked correctly. Is there anything I can do about this?
RE: In a pivot table,
Yes. This is a little awkward, but it does have a simple fix.
When changing the 'Value Field Settings', which is how you changed it from a count to a sum, there will also be a box that says 'Number Format'. Here you change the formatting type to a 'Time', and it should resolve all your times properly.
A couple of things to keep in mind when doing this:
1. The default time format has seconds as well. In order to just show the time as hours and minutes, you need to go to custom format and change the format to "hh:mm"
The grand total at the bottom of your pivot table may look like it is wrong when in a date format. This is because it deals in a 24 hour clock, so if your total goes over 24 hours then it rolls over to the next day and starts from 0 again. There isn't a good way to resolve that within the pivot table itself. But if you have a separate cell where you add a formula that (=Total*24), then format it as a normal number format, then that will give you the actual total amount of hours.
RE: In a pivot table,
Yes. This is a little awkward, but it does have a simple fix.
When changing the 'Value Field Settings', which is how you changed it from a count to a sum, there will also be a box that says 'Number Format'. Here you change the formatting type to a 'Time', and it should resolve all your times properly.
A couple of things to keep in mind when doing this:
1. The default time format has seconds as well. In order to just show the time as hours and minutes, you need to go to custom format and change the format to "hh:mm"
The grand total at the bottom of your pivot table may look like it is wrong when in a date format. This is because it deals in a 24 hour clock, so if your total goes over 24 hours then it rolls over to the next day and starts from 0 again. There isn't a good way to resolve that within the pivot table itself. But if you have a separate cell where you add a formula that (=Total*24), then format it as a normal number format, then that will give you the actual total amount of hours.
