If you try to show hundredths of second, or tenths of second in pivot table, you might have trouble. Usually, the times are rounded, and there is a zero, instead of numbers, for the tenths and hundredths.
See how to fix that pivot table time problem, so that the numbers are displayed correctly.
Pivot Table Times are Rounded
In the screen shot below, you can see what usually happens if you add times in a pivot table – the tenths and hundredths are rounded. There are zeros where the tenths and hundredths should be.
In the source data, the maximum time value for team A is outlined in red. The pivot table is set to show the maximum time for each team.
For team A, the max time shows as 05:15:25 in cell C2 (the source data). However, in the pivot table, it is 05:15:00, even though the same number formatting has been applied to the Time field.
Add a New Time Field
To fix this problem, add a new field in the source data, with a simple link to the original time field.
In the screen shot below, the new field is named TimeCalc. The formula in cell D2 is a link to the original time in the column to the left: =C2
Instead of using a time format for the TimeCalc field, apply the General format to that column.
Show Tenths of Second in Pivot Table
Next, refresh the pivot table, so the new field, Timescale, appears in the PivotTable Field List.
Drag the TimeCalc field into the Values area of the pivot table.
Then, apply a time format to the TimeCalc field, to show the tenths of a second, or hundredths of a second. For example, use: m:ss.00
In the screen shot below, both the original Time field, and the new TimeCalc field are in the pivot table. The TimeCalc field shows the correct number, with the hundredths of a second.
The original Time field can be removed from the pivot table.
More Pivot Table Time Problems
To see more pivot table time problems, and to download the sample file, go to the Pivot Table Time Values page on my Contextures site.
Watch the Pivot Table Time Rounding Video
Watch this short video to see the steps for fixing a Pivot Table time rounding problem, to show hundredths or tenths of second in pivot table.
In the video:
0:00 Introduction
0:12 Build a Pivot Table
0:55 Format the Time
1:38 Fix the Time Problem
2:43 Format the New Field
3:35 Get the Sample File
________________