Below is a print screen of a pivot table with 3 columns:

1. First column is 'Open Date'
2. Column 2 is 'Closed Date'
3. Column 3 is the number of days betrween the two, as a calculated field: =datedif('Open Date','Closed Date',"d")

Here is a pic:



JFYI...the only place I am allowed by Excel to put the calculated field is in the sigma box (values).

What I want to do is Group the open date (month). Then I want an average of the open days for that month. I'm having problems coming up with the calculated field that will work.

I have added a column to the data for testing purposes that contains the date difference for each date. I have that displayed in a picture here, after grouping on Open Date by month:



What you will see is that the third colum, which is the calculated filed per item 3 above, now has very large numbers, which are summed. The second column shows the actual days difference between the two. I would think that column 3 should be the total of those 5 days for January, but obviously it is not. If the third column was the total, I would expect to change it to average instead of sum in the sigma field, but changing it to average makes zero difference; even if it did make a difference, it would be wrong, as that the number in that field is obviously much higher than the total of the date difference of the 5 days in January .

So I definitely have something to learn here. Hoping someone can help.