Fix Excel Pivot Table Time Field Problems

Tips for working with Excel pivot table time fields and fixing time problems. Prevent pivot table time rounding, Fix pivot table total time amounts.

Show Times With Tenth or Hundredth of Second

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

Video: Fix Pivot Table Time Problem

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

Fix Time Rounding

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.

New Column in Source Data

• 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.
• Format this column as General instead of Time.
  • The times will be displayed as numbers, with several decimal places

Add New Field to Pivot Table

• 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

Compare the Time Fields

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

Show Total Time in Pivot Table

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.

How to Fix the Total Time

To fix the project time subtotals, follow these steps, to format the cells with a custom number format:

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

Correct Total Time in Pivot Table

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

Video Transcript: Fix Pivot Table Time Problem

This is the full transcript for the How to Fix Pivot Table Time Rounding video shown above, on this page.

'----------------------

How to Fix Excel Pivot Table Time Rounding

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.

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

Format the Time

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.

Format the Time

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.

Format the New Field

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.

Get the Workbook

For more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website at www.contextures.com.

Get the Sample File

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.

More Pivot Table Resources

Pivot Table Introduction

Summary Functions

Running Totals

Clear Old Items in Pivot Table