Formatting Duration Fields in Excel Pivot Table Reports
Many Excel Reports use Pivot tables or Pivot Charts. These need to be formatted correctly. Set up your excel report to include the report Type, Dates and Data you require then Generate the Report Template (as shown below)
In the generated Template, on the second Worksheet (that has the data export) right click on your Duration fields and see how the fields are formatted. This is the EXACT cell formatting you need to apply to the Duration fields you use in a pivot table or pivot chart.
Now look at the format of the cells. Duration Formats are not Numbers, but in the original data that is exported from the Excel file, format the fields as Numbers. You
The [ ] around the highest duration ensures that if you are summing durations in a pivot table, then the answer will calculate and show in the ss.SSS format, even if the total goes about 60 seconds. If you were summing 400m split times for an 800m, without the [ ] around the ss then a time of 46.89sec and a 48.11sec would sum to 1:35.000(mm.ss.SSS), which would display as 35.00 (in a ss.SSS format and you would not see the minutes). With the [ ] the result would show as 93.000 seconds.
On the First Worksheet, add in the pivot table and remember to choose the "All fields" from the "Use in Formula" (On the Formulas Tab)
Now add in the fields into the Pivot Table. You will notice that the duration field cells are NOT formatted correctly when they are added
You need to select the Pivot table columns that are duration fields and then right mouse click and select Format Cells. Then apply the correct custom format (ensure it is the same as the format of the exported data in the data worksheet) e.g [ss].000 .
You MUST select a custom field with the [ ] around the duration to ensures that if you are summing durations in a pivot table, then the answer will calculate and show all of the duration field correctly. If you use a ss.SSS format (without the [ ]) and the sum or average goes above 60 seconds, then ONLY the seconds will show and the minutes field will be hidden. E.g 1:44.555 in a ss.SSS format will ONLY show the seconds field of 44.555. If you add in the [ ] around the ss then it will display as 104.555. Similarly, if you were summing 400m split times for an 800m, without the [ ] around the ss then a time of 46.89sec and a 48.11sec would sum to 1:35.000(mm.ss.SSS), which would display as 35.00 (in a ss.SSS format and you would not see the minutes). With the [ ] the result would show as 93.000 seconds.