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.
- In the pivot table, right-click a cell in the Sum of Hours field
- Click Value Field Settings
- In the Value Field Settings dialog box, click Number Format
- In the Category list, click Custom
- In the Type box, enter: [h]:mm
- Click OK, twice, to close the dialog boxes.
The pivot table now shows the correct total hours worked on each project.