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
- In the pivot table, right-click on the Sum of Hours field button
- Click on Field Settings
- Click the Number button
- In the Category list, click on Custom
- In the Type box, enter: [h]:mm
- Click OK, twice, to close the dialog boxes.
- 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.
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
I am having the same issue as Daniel. Can anyone help?
Joanne, can you email me a sample file that shows the formatting problem?
ddalgleish AT contextures.com
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.
Thanks a lot! Works like a charm. 🙂
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!
I also have the same trouble as Daniel, anyone have a resolution for this?
Hello everyone. The [h]:mm:ss is a CONVERSION that converts a “decimal part of day” (with 1.0 being 24:00:00 or 1 day), so the field that you “format” using the [h]:mm:ss (or just [h]:mm) converts the filed using the following logic:
for [h]:mm -> number minutes in a day ( 24 * 60 = 1440 ) -> 1440 = 1
for [h]:mm:ss -> number of seconds in a day ( 1440 *60 = 86400 ) -> 86400 = 1
So, you have to change the “number of minutes or seconds” to a “Decimal part of Day” by dividing your Minutes or Seconds by the TOTAL shown above. This will always return the correct Hours:Minutes:Seconds if you do so.
Hope this helps explain the “[h]” format function. I have used it successfully for many years this way. Although I have a minor glitch when using a “Pivot Table Calculated Field” using a Formula to return the Total Hours in a Year using this “Leap Year” formula that I ran across (and formatting it with the [h] {duration} format):
=IF(((MOD(YEAR,4)=0)*((MOD(YEAR,100)0)+(MOD(YEAR,400)=0))=1),366,365)
I think this formula is incorrect when using a calculated field for the year. I have checked the data when it calculates incorrectly and all the year fields are the correct value. I am looking for a better “Number of days in a year” formula.