Timesheet Hours not adding correctly in a Pivot Table

I must be missing some time formula or something with this problem.

I'm using a pivot table to show weekly hours employees work. I've grouped the rows of the pivot table by dates, starting with a Sunday so they come out with one row per week.

The sum of the hours worked for each day seem to carry over the date in the pivot table and I don't want that. I

'm able to display the total hours using a custom format of "d - hh:mm" but I really want total hours:minutes.

How do I remove the date in the pivot table to only show the hours worked in that week.

See the attached showing what I have and want.

I want 2 days 4 hours and 10 minutes to show as 52:10 or 52.17 in the Pivot Table.

I don't know the reason why Excel Pivot Table calculates "Sum of Hours" to a too big result:
In your workbook, cell I8, Bob 12/28/2009 - 01/03/2010, it is January 2, 1908, it means 1462 days, and 4 hours, 14 minutes.
You use a custom format d - hh:mm and it will be 2 - 04:14

But the exact number is only 4 hours and 14 minutes for only the date Fri 01/01/2010.

It is because the filed "Hours" (column E) is fomated as Time.

In attached file, I format another field (G5 = E5) as Decimal Number (General), then Pivot Table calculated to the correct result.

Finally, I use custom format [hh]:mm and the result will be for example 28:04 in Bob 1/11/2010 - 1/17/2010.

Hi ptm0412

Thanks for looking, but I don't believe your answer is correct.

If I filter for only Monday by making the filter above the pivot table a 2 - You get 4hrs and 33 minutes but it should be 4hrs and 35 minutes for the first weeks totals.

I've tried MRound() without success and I realize my d - hh:mm format is just crap as it was wrong from the beginning.

I think this problem is still open and I'm looking for a better explaination.

Hi MarvinP,

I have said that I dont't know the reason, but I think you should compare the result to see If I correct or not:
As you filter Monday only (weekday =2), see the result of Bob Jan 04 - Jan 10:

- In cell I9, see the formula bar: 01/02/1908 04:35:00, how many hours and minutes do you think? If you format it as [hh]:mm it will be 35092 hours and 35 minutes, because from 01/01/1900 to 01/02/1908 is 1462 days!
- You format cell as d - hh:mm, and Excel displays 2 - 04:35, and you think it is 52 hours and 35 minutes?
- I use another field that is totally equal to your field, but format in General, and the result displayed at cell J9 is 04:33, and in the formula bar is 04:33:36.

Infact, it is 4 hours and 35 minutes, the difference between it and my field is about 1 minute and a half, equal to 0.0010416. Do you accept this? Or you prefer 52:35?

Anyway, if you format my added field "Decimal Hours" (column G) with 5 decimal places or more, it will result exactly 04:35.

Regards,
ptm0412

Originally Posted by MarvinP

You get 4hrs and 33 minutes but it should be 4hrs and 35 minutes for the first weeks totals.

Check "Precision as Displayed" settings

The Precision As Displayed is checked and didn't seem to help, one way or the other.

It looks like the Sum of Hours in the Pivot Table is also using the Date in the summation. By changing the number format of the Pivot Table's Sum of Hours to [h]mm:ss from d - hh:mm it changes from:
2 - 14:00 to 35092:14:00 . Where did this 35092 come from? The 2 days should be 48 hours.

I'm still clueless.

Originally Posted by MarvinP

The Precision As Displayed is checked and didn't seem to help, one way or the other

@MarvinP, the suggestion was made in relation to ptm0412's suggestion (number format source).

Once Precision as Displayed is deactivated the Pivot will return the correct results.

edit: to clarify - this stems from use of Time formats running a Pivot on 1904 Date System with Time formatted source data (don't ask me why - just is an issue)

Hey DO,

Yes it was the 1904 setting. After changing it and redoing the data and refreshing the pivot table things worked as hoped.

The Precision As Displayed didn't improve the problem until the 1904 system was unchecked and I went back to the "better" 1900 format. This now works correctly as attached.

Originally Posted by MarvinP

Yes it was the 1904 setting. After changing it and redoing the data and refreshing the pivot table things worked as hoped.

The Precision As Displayed didn't improve the problem until the 1904 system was unchecked and I went back to the "better" 1900 format. This now works correctly as attached.

I guess we will have to agree to disagree.

Attached is earlier file, the only modifications to which are:

a) underlying number format of source values
b) deactivation of Precision as Displayed

ie 1904 Date System persists

Note: changing date systems like Precision as Displayed is of course risky depending on other functionality already in place

Hi DO,

Based on my signature line of "One test ..." I got my original post, saved it to my desktop, unchecked the Precision as Displayed, reset the Data Range and Refreshed All on the Pivots. I left the 1904 box checked. The attached is my result. I guess it is still an open question. Logical Minds Can Disagree, although I seem to lose most of these debates to you.

Originally Posted by MarvinP

2 - 14:00 to 35092:14:00 . Where did this 35092 come from? The 2 days should be 48 hours.

The 2 days will be 48 hours only if the date on the formular bar is 01/03/1900 00:00 (Jan 03, 1900) or 01/03/1904 00:00 in 1904 systems

Now, the formula bar displays 01/02/1908 04:14, that means 1462 days after 01/01/1904 equal to 35088 hours + 04 hous = 35092 hours.

As I said before, I still don't know why.

Hi ptm0412,

DonkeyOte kept saying you were right in pointing to the problem. I do believe it is the 1904 setting I had in my Advanced Options.

I'm now much happier about getting a correct answer with the [h]:mm format.

Originally Posted by MarvinP

Based on my signature line of "One test ..." I got my original post, saved it to my desktop, unchecked the Precision as Displayed, reset the Data Range and Refreshed All on the Pivots. I left the 1904 box checked. The attached is my result. I guess it is still an open question.

The point was / is - you must first adjust the underlying format of your source data values. If you format E as General and refresh the Pivot what then ?

It should be noted that though the issues here were primarily due to use of 1904 Date System working with Dates in Pivots can prove problematic irrespective of Date System and often issues can be circumvented by adjusting the underlying format of source values - don't ask me why.
http://www.mrexcel.com/forum/showthread.php?t=518846

There seems to be little in the way of MSKB documentation covering these type of issues - it is just something to bear in mind when working with DateTime related info in Pivots (ie not bulletproof)