Creating a calculated field in a pivot table for time difference

[TamirBasin]

Hi,

I am getting the wrong results in my pivot table's calculate field.
I am trying to have a calculated field which gives me the difference between StartDate and EndDate (a promotion sale period).

I would appreciate the help.
Here is a link to the excel demo file:
https://drive.google.com/open?id=0Bzz1wn6UL_b6Zk5ldXdXM0lSZU0

and a print screen of that file:
https://drive.google.com/open?id=0Bzz1wn6UL_b6ektnd2dxakd6UWc


Thank you,
Tamir

 

[istiasztalos]

Hi,

Calculated Fields are always calculated as SUM, so it would add up the days difference as many times as you have it in the data. One quick workaround I can think of and works for sure, is to add a count column to your data for example with the name 'count' (just add a new column with formula =1)

Then the calculated field for your pivot table is '=(EndDate-StartDate) /count'

 

[steve the fish]

I think the only way you could do that is by adding a helper column to your table. It needs to contain just a header and then a 1 for each row. This can then be used as a count in the calculated field. Lets say the field was called 'Column1'. Your calculated field equation:

=(EndDate -StartDate )/Column1

 

[TamirBasin]

Thank you istiasztalos and steve the fish
It works great with the helper column = 1.

Tamir