Excel Pivot Table Time Field

Tips for working with pivot table time fields.

Show Times With Tenth or Hundredth of Second

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.

To correctly display the times in the pivot table, you can use the following workaround:

• In the source data, add a column, TimeCalc, with a formula that refers to the time column, for example, =C2.
• Format this column as General instead of Time.



• Refresh the pivot table, and add the TimeCalc field to the Value area
• Format the TimeCalc with the custom number format of m:ss.00.
• Remove the original time field

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.

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

1. In the pivot table, right-click a cell in the time field, and click Value Field Settings
2. In the Value Field Settings dialog box, click Number Format
3. In the Category list, click Custom
4. In the Type box, enter: [h]:mm   (That format totals the hours)
5. Click OK, twice, to close the dialog boxes

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.

Download the Sample File

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.

More Pivot Table Resources

Add-In: PivotPower Premium

Pivot Table Introduction

Summary Functions

Running Totals

Clear Old Items in Pivot Table