PivotTable 'Summarize Values By' missing Average and Sum for time/duration fields after recent update

Sayantan Datta 5 Reputation points
2026-09-29T09:46:52.0733333+00:00

Excel version: Excel 2021 Version 2609 Build 16.0.20430.20032 (64-bit). The issue started approximately 7 days ago. Multiple Windows users are experiencing the same behavior. A sample dataset with hh:mm:ss format reproduces the problem.

User's image

Microsoft 365 and Office | Excel | Other | Windows
0 comments

4 answers

Sort by: Newest
Most helpful Newest Oldest
  1. ROBERT Chrystelle - Fromont Briens 0 Reputation points
    2026-10-02T11:57:31.7233333+00:00

    Bonjour

    Nous rencontrons le même problème. Nous sommes sous O365

    Was this answer helpful?

    1 person found this answer helpful.
  2. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2026-10-01T15:17:45.8233333+00:00

    I can confirm that this is a bug in Excel. It affects not only Excel 2021 but at least Excel 2024 as well; only "Count" is available here.
    grafik

    The only workaround I found so far is to format the data cells (column B in this example) as General or any other number format, refresh the Pivot table and all the aggregate functions become available.

    grafik

    Hope this helps.

    Please report this issue as usual via File\Feedback
    https://support.microsoft.com/office/how-do-i-give-feedback-on-microsoft-365

    Andreas.

    Was this answer helpful?

    3 people found this answer helpful.
  3. Sayantan Datta 5 Reputation points
    2026-10-01T05:55:38.69+00:00

    Still same issue User's image

    Was this answer helpful?

  4. Xavier-D 13,745 Reputation points Microsoft External Staff Moderator
    2026-09-30T02:18:30.65+00:00

    Since this is a public forum, we will hide your personal identifiable information, and I have sent that image of yours containing your personal identifiable information in a private message so we can work together to resolve the issue. 

    Hello Sayantan Datta,

    Since the issue started after the recent update and can be reproduced with a new sample dataset, this may be related to the current Excel build. However, first verify whether Excel still recognizes the duration values as numbers.

    In an empty cell, enter:

    =ISNUMBER(A2)

    Replace A2 with one of the affected duration cells.

    • If the result is FALSE, the durations are stored as text. Create a helper column using =VALUE(A2), format the results as [h]:mm:ss, and use that helper column in the PivotTable.
    • If the result is TRUE, create a new PivotTable from the same source data without selecting Add this data to the Data Model. Then check whether Sum and Average appear under Value Field Settings.

    You can also try refreshing the PivotTable or rebuilding it from a newly created Excel table.

    If the options are still missing for confirmed numeric values in a newly created PivotTable, this is more likely to be a build-specific regression rather than a workbook formatting issue. Because several users can reproduce it following the update, please report it through File > Feedback > Report a Problem and include the Excel version, build number, a sample workbook, and the approximate date the behavior began.

    Feel free reply back to this post if you need further assistance, I'll be happy to help.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.