Show Total Hours in a Pivot Table

In an Excel file, you might record the time that employee work on specific projects. For example, an employee worked on Project A for 8 hours Monday, 8 hours Tuesday, 6 hours Wednesday and 3 hours Thursday, for a total of 25 hours.

If you create a pivot table from the time data, and show the total time per project, Project A shows 1:00 as the total, instead of 25:00.


Why is the total one hour, instead of 25? 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 A Total represents the 1:00 AM time.

Apply a custom time format

To fix the project subtotals, you can format the cells with a custom number format – [h]:mm – and they'll total correctly.

  1. In the pivot table, right-click a cell in the Sum of Hours field
  2. Click Value Field Settings
  3. In the Value Field Settings dialog box, click Number Format
  4. In the Category list, click Custom
  5. In the Type box, enter: [h]:mm
  6. Click OK, twice, to close the dialog boxes.

The pivot table now shows the correct total hours worked on each project.



You may also like...

12 Responses

  1. Joos S says:

    Could anyone please assist with the totalling of time and date I have.
    Cell A1 10/01/11(ddmmyy)Cell B1 20:00 Cell C1 12/01/11 Cell D1 01:30 that equates to (1day 5:30)the days and Hrs could be split across 2 cells if that will make it easier. 1day (cell F1) HRS and Minutes in Cell G1
    The second is to add the column (F1:F23) = total days and add column (G1:G23) = Total Hrs
    Can that be achieved.

    Joos S

  2. RoadSector says:

    good job! thanks for the help

  3. Mike says:

    Thanks, this worked great for me and was driving me crazy.

  4. Qureeb says:

    Nice way of the learning

  5. Sara says:

    Every day’s a school day! thanks for the excellent explanation

  6. Parthiban Raja says:

    Thanks a lot. It’s worked as expected.

  7. Rahul says:

    yes this functionality is working..thanks for your suggestion.

  8. Sreeramulu says:

    Thank you .. it worked for me.

  9. Marijan says:

    tnx a lot.

  10. Edward says:


  11. shakeer says:

    this my question every day 9.5 hrs regular working hrs including 1.5 hrs hunch break now i need a summary in pivot that daily OT hrs, and the total OT hrs for the month.
    B# Date Clock In Clock Out ATT_Time
    44002000 01/21/2018 05:47 17:52 12:04
    44002000 01/22/2018 05:51 17:54 12:02
    44002000 01/23/2018 05:51 17:52 12:01
    44002000 01/24/2018 05:50 17:51 12:00

Leave a Reply

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