Question: im new to the pivot table thing and cant quite get my head around it. We use a telematics system for tracking our fleet and there are geofences on site that measure time and overtime. i have attached a spreadsheet i use seeking some help as i can not get it to work for time as in hh:mm:ss
i would like to be able to have each site in the table shown as a total average time on site, and also a total overtime on site.
To keep it fairly straightforward I would create on table as shown in the attached file in Sheet1. It has two time columns. One for the entry-exit duration (to be averaged) and one for overtime (to be summed). And the pivot table beside it does what you need.
By default, Excel sums all numeric values, but you can over-ride that in the Value Field Settings dialogue that opens by right-clicking on a number in the pivot table.
Finally, it's important to set the correct Custom format to these time values. By default, Excel will treat them as clock time. They wrap back to zero every 24 hours. For example 40 hours will be displayed as 16:00:00 (hh:mm:ss) as it is 16 hours into the 'new day'. You want duration and must therefore use a custom format with square brackets around the hh part, thus [hh]:mm:ss. This will display 40:00:00
Of course, you can also keep the tables as they are and create two separate pivot tables.
@riny That is awesome. Worked perfectly. Thank you for that.