Pivot Table Issues

How to Keep Excel Pivot Table Durations Formatted as [h]:mm

John Wilson Sep 28, 2026 869 views

Question details

The user needs to maintain duration values in a Pivot Table formatted as [h]:mm, ensuring they remain numeric for calculations like averages even after pasting new data and refreshing.

How to Keep Excel Pivot Table Durations Formatted as [h]:mm

Product: Excel
Device & OS: not provided
Scenario: Summarizing and analyzing time duration data across multiple Pivot Tables and updating templates with new source records.
Observed behavior: Excel converts the numeric duration display to a standard time of day format (e.g., 12:45:00 AM) in Pivot Tables upon refreshing or pasting updated records.

Before you start

Ensure that the source data for your durations is entered as standard Excel time values (fractions of a day) rather than text strings, as Pivot Tables cannot perform numeric calculations on text.

Solution 1Recommended

Set Custom [h]:mm Format in PivotTable Value Field Settings

Applying the custom time format directly to the Value Field Settings ensures the formatting is permanently preserved even when the Pivot Table is refreshed.

Applying standard cell formatting from the Home tab is often overridden when a Pivot Table updates. Using the Field Settings guarantees that the data retains its [h]:mm structure for accurate summaries.

Access Value Field Settings

Right-click on any duration value inside your Pivot Table and select 'Value Field Settings' from the context menu.

Open Number Format

In the Value Field Settings dialog box, click on the 'Number Format' button located at the bottom left.

Apply Custom Format

Select 'Custom' from the Category list on the left side of the Format Cells window.

Enter the [h]:mm Code

In the 'Type' text box, enter [h]:mm and click 'OK' twice to apply the changes to your Pivot Table.

Set Custom [h]:mm Format in PivotTable Value Field Settings

Format Preserved: Your duration data will now maintain the [h]:mm format and remain fully numeric for calculations, regardless of how often you paste new records and refresh.

Solution 2

Apply [h]:mm Formatting to the Source Data

Formatting the underlying worksheet data correctly establishes a clean baseline for any new Pivot Tables generated from the dataset.

Efficient Data Analysis

Format Pivot Tables Easily in WPS Spreadsheet

WPS Spreadsheet provides robust Pivot Table features that perfectly support custom time and duration formats. You can effortlessly manage numeric data, calculate averages, and refresh your tables without losing your carefully applied [h]:mm formatting.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your source data and Pivot Tables.
2. Open Value Field Settings: Right-click on the duration data within the Pivot Table and choose 'Value Field Settings'.
3. Access Formatting Options: Click the 'Number Format' button at the bottom of the dialog to modify the field's display.
4. Apply the [h]:mm format: Select 'Custom', type [h]:mm in the provided box, and click 'OK' to lock in the permanent formatting.

Fully compatible with Microsoft Excel (.xlsx) Pivot Tables and custom number formats.Easily preserve custom formatting like [h]:mm through dedicated Field Settings.Lightweight, fast, and highly intuitive interface for seamless data analysis.Free to download and use as a powerful alternative for your daily spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show my duration as a time of day in the formula bar?

Excel stores all dates and times as numeric fractions of a single day. Regardless of how you format the cell (such as [h]:mm), the formula bar is designed to display that underlying numeric value as a standard time of day (e.g., 12:45:00 AM). This is normal behavior and will not affect your calculations.

Why do my Pivot Table formats disappear when I click refresh?

If you apply cell formatting directly using the Home ribbon, Excel considers it temporary and often overwrites it when the Pivot Table rebuilds during a refresh. To make formatting stick permanently, you must apply it through the 'Value Field Settings' > 'Number Format' menu.

Can I calculate the average of duration times in a Pivot Table?

Yes. As long as your source durations are recognized as numeric time values rather than text, you can change the summarization method in the Value Field Settings from 'Sum' or 'Count' to 'Average'. Ensure the Field Setting number format is set to [h]:mm to display the result correctly.

Excel & Data Problems