Tips for working with pivot table time fields.
If a pivot table's source data has time fields, you can format that field to show tenths of a second. You can also use a custom number format of m:ss.00, to show hundredths of a second.
The time formats show correctly in the worksheet, for example, 5:15.25. However, if the time fields are added to the pivot table, they are rounded in the pivot table, with all thetenths and hundredths showing as zero, for example, 5:15.00.
Even if you carefully apply the correct number format to the pivot field, the rounding is not affected, as shown in the screen shot below.
Watch this short video to see the steps for fixing a Pivot Table time rounding problem. The written instructions are below the video.
In the screen shot below, the original time field is rounded, and the TimeCalc field correctly shows the time to the hundredth of a second.
If you create a pivot table from the time data, and show the total time, the totals might appear to be incorrect. In the screen shot below, there were 25 hours of work done on project B, but the pivot table shows 1:00 as the total.
The problem occurs because the pivot table subtotals are shown as time rather than total hours. From the 25 hours, the first 24 hours are counted as one day, and the remaining hour is 1:00 AM of the second day. The 1:00 in the Project B Total represents the 1:00 AM time.
To fix the project subtotals, follow these steps, to format the cells with a custom number format:
With the custom number formatting applied, the pivot table now shows the correct total hours worked on each project, and there are 25 hours for project B.
You can download a copy of the Pivot Table Time Values sample file, to see the data and the pivot tables. The zipped file is in xlsx format, and does not contain macros.
Last updated: December 13, 2016 11:28 AM