Totaling Hours in a Pivot Table Time Field

In your source data, you might record the amount of time that employees have spent working on projects. For example, Jim worked on Project A for 8 hours Monday, 8 hours Tuesday, 6 hours Wednesday and 3 hours Thursday, for a total of 25 hours.

In the pivot table, you want the total time per project, and Project A should show a total of 25 hours.

However, your pivot table shows 1:00 as the total, instead of 25:00.

This happens because the results are shown as time rather than total time. 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 A Total represents the 1:00 AM time.

To see the total time, format the cells that contain total times with the custom number format [h]:mm, and they’ll total correctly.

To apply the custom format

  1. In the pivot table, right-click on the Sum of Hours field button
  2. Click on Field Settings
  3. Click the Number button
  4. In the Category list, click on Custom
  5. In the Type box, enter: [h]:mm
  6. Click OK, twice, to close the dialog boxes.
  7. The pivot table will now show the total time for hours worked on each project.


For more information on pivot tables, see the PivotTable Topics on my Contextures web site.

This entry was posted in Group and Total and tagged , , . Bookmark the permalink.

7 Responses to Totaling Hours in a Pivot Table Time Field

  1. Daniel Black says:

    Hi all im having some real trouble calculating time used spent on specific projects through pivot tables.

    I have formatted the cells with [H]:mm but instead of telling me a caseworker spent 1hr and 35 (1.35)mins completing a piece of work it appears as 192.00

    I dont know how to fix this and it is really annoying me now

  2. Joanne B says:

    I am having the same issue as Daniel. Can anyone help?

  3. Debra Dalgleish says:

    Joanne, can you email me a sample file that shows the formatting problem?
    ddalgleish AT

  4. Luciano K says:

    Hi, I’m having the same problem that Joanne B had. Debra I sent you a sample file, do you think you can take a look at it? Thanks in advance, I really appreciate it.

  5. rbaleksandar says:

    Thanks a lot! Works like a charm. 🙂

  6. Pablo says:

    What if my data in the Pivot table is in MINUTES and I want it to show in HOURS. It really would be a conversion. For example my MINTUES is 1609, but I want it to display 26.81

    I have tried the [h]:MM format but that does not do it using a custom calculation inside the pivot table?

    Great site!

  7. Daniel Morris says:

    I also have the same trouble as Daniel, anyone have a resolution for this?

Leave a Reply

Your email address will not be published. Required fields are marked *