Home > Pivot > Format > Time Fields Fix Excel Pivot Table Time Field ProblemsTips for working with Excel pivot table time fields and fixing time problems. Prevent pivot table time rounding, Fix pivot table total time amounts. |
If a pivot table's source data has time fields, you can format that field in the source data, to show tenths of a second. You can also use a custom number format of m:ss.00, to show hundredths of a second.
For example, in the screen shot below, the time formats show correctly in the cell C2 on the worksheet -- 05:15.25
However, if the time fields are added to the pivot table, they are rounded in the pivot table.
Watch this short video to see the steps for fixing a Pivot Table time rounding problem, in a sample workbook that you can download below. The written instructions are below the video, and the full video transcript is at the end of this page
Video Timeline
To correctly display the times in the pivot table, so they show tenths of seconds and hundredths of seconds, you can use the following workaround.
In the screen shot below, the original Time field shows a rounded time - 05:15:00
However, the new TimeCalc field correctly shows the time to the hundredth of a second: 05:15:25
If you create a pivot table from the time data, and show the total time, the totals might appear to be incorrect.
For example, in the screen shot below:
Pivot table Grand Totals might also show an incorrect amount, like the 8:45 total hours in the screen shot below.
The problem occurs because the pivot table subtotals and grand totals are shown as time rather than total hours.
To fix the project time subtotals, follow these steps, to format the cells with a custom number format:
With the new custom number format applied, the pivot table now shows the correct total hours worked on each project
In the screen shot below, you can see the correct total hours for all three projects:
The Grand Total also shows the correct amount of total project hours: 80:45
This is the full transcript for the How to Fix Pivot Table Time Rounding video shown above, on this page.
'----------------------
This table has times that are race results for three different teams.
I'm going to build a pivot table from this data and we'll see a problem that occurs when you put time into a pivot table.
So first to build the pivot table:
For the pivot table:
So showing the maximum time, I don't want it in this General format, I'm going to put it into a time format.
But instead of showing this time as 0.2 or 0.3, whichever way it's going to round it, it's just showing a zero here.
It's not showing us the numbers that are the tenths or the hundredths of a second.
To fix this problem in a pivot table, there is a workaround that you can use.
And in this cell I'm just going to link to the original Time formula
Now this is General format and that's the way we're going to leave this.
Over here, we can see that there's a custom time format, and that's what's causing the problem in our pivot table.
Now I'm going to use the same format that we tried to use here.
Let's change that to Max.
And now we've got the 5 minutes, 15 seconds, and now it's showing correctly. Instead of the zero, we're getting the tenths of a second here.
So once you've got the new field working correctly, you could take out the old field.
For more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website at www.contextures.com.
You can download a copy of the Pivot Table Time Values sample file, to see the data and the completed pivot tables. The zipped Excel workbook is in xlsx format, and does not contain any macros.
Last updated: October 30, 2022 1:59 PM