Tips 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.

- All of the tenths and hundredths show as zero,
for example, in cell F4, the time shows as:
**5:15.00** - Even if you carefully apply the correct number format to the pivottable fields, 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, 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**

- 00:00 Introduction
- 00:14 Build a Pivot Table
- 00:57 Format the Time
- 01:39 Fix the Time Problem
- 02:44 Format the New Field
- 03:24 Get the Workbook

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 source data, add a new column. In this example, the column heading is TimeCalc
- In the first data row for the new column, enter a simple formula that refers
to the time column.
- In this example, the time is in column C, and this formula is in cell D2:
**=C2**.

- In this example, the time is in column C, and this formula is in cell D2:
- Format this column as
**General**instead of**Time**.- The times will be displayed as numbers, with several decimal places

- Next, refresh the pivot table
- Then, add the TimeCalc field to the Value area
- Format the TimeCalc with this custom number format:
**m:ss.00** - Remove the original time field

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:

- There were 25 hours of work done on project B
- The pivot table shows 1:00 as the Project B total, instead of 25:00

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.

- From the 25 hours, the first 24 hours are counted as one day
- 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 time subtotals, follow these steps, to format the cells with a custom number format:

- In the pivot table, right-click a cell in the time field
- Click on Value Field Settings
- In the Value Field Settings dialog box, click Number Format
- The Format Cells dialog box opens, with only the Number tab showing
- In the Category list at the left, click Custom
- In the Type box, enter this format:
**[h]:mm** - That format, with the square brackets, totals the hours
- Click OK, twice, to close the dialog boxes

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:

- Project A: 28:00
- Project B: 27:45
- Project C: 25:00

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:

- I'll select any cell here
- Go to the Insert tab, Pivot Table.
- It has selected my table, we can see in the background.
- I would like this on an existing sheet, so click and click a starting cell and OK.

For the pivot table:

- I want the ID field in. So that's the team ID letter
- And I want the time, so I'm going to put that into the values.
- It's showing up as a count of the time and I would like it as the max.
- So I'll right click Summarize Values by Max.

So showing the maximum time, I don't want it in this General format, I'm going to put it into a time format.

- Right click, Value Field Settings.
- And in here I'll go to a Number Format.
- Time and I would like the tenths of a second to show.
- So I'll select this time and click OK.
- I'll click OK again, and there are the times.

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.

- I'm going back to the source data, and I'm going to put in another column here.
- So I'll click where I want the heading. I'll call this TimeCalc.
- When I press Enter, it automatically expands the table to include that column.

And in this cell I'm just going to link to the original Time formula

- So equals, and click on cell C2
- That's the time field.
- Or you could type =C2, if you'd rather have a cell reference
- Press Enter, and it fills down.

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 refresh the pivot table so that it understands we've got a new field
- Right-click and Refresh, and here's our TimeCalc field
- So I'll put that into the Values area and it shows up in that General format.

Now I'm going to use the same format that we tried to use here.

- Right-click, Value Field Settings, go into Number Format,
- And in here I'll go to Time. I'll pick the same format we tried to use before.
- Click OK. OK.

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: January 31, 2022 12:52 PM