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.

Could you please post a sheet containing sample of your data ?

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Sure, fiel is attached. First tab is date; 2nd tab is data as per first print screen; 3rd tab is data as tab in 2nd print screen.

I have notes in the file that hopefulluy explains what I am trying to do.

Appreciate yoru input.

Attached Files

Example.xlsx (263.5 KB, 10 views)