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.

PivotTotalTime01

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
    ValueFieldSettings
  3. In the Value Field Settings dialog box, click Number Format
    PivotTotalTime02
  4. In the Category list, click Custom
  5. In the Type box, enter: [h]:mm
    PivotTotalTime03
  6. Click OK, twice, to close the dialog boxes.

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

PivotTotalTime04

_______________

2 comments to Show Total Hours in a Pivot Table

  • Joos S

    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.

    Regards
    Joos S

  • RoadSector

    good job! thanks for the help

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>