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. Later, you can show total hours in a Pivot Table.
Pivot Table Times
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.
_______________
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
good job! thanks for the help
Thanks, this worked great for me and was driving me crazy.
@Mike, Great! Thanks for letting me know that it worked for you.
Nice way of the learning
Every day’s a school day! thanks for the excellent explanation
Thanks a lot. It’s worked as expected.
yes this functionality is working..thanks for your suggestion.
Thank you .. it worked for me.
tnx a lot.
GREAT JOB! YOU SAVED MY LIFE. THANXXXX SIR!
Thanks for the great post! Exactly what I was looking for!
Great, extremely helpful
thank you very much.