How to calculate the difference between max/min dates on non static pivot tables?

user_9940344 0
Viewed 1149

I have a pivot table like this

A | 01/01/2001 | 01/02/2001
A | 01/01/2001 | 01/02/2008
B | 01/01/1994 | 01/11/2015
B | 01/01/2006 | 01/02/2014
B | 11/01/2016 | 19/07/2018
C | 01/03/2006 | 21/02/2014
C | 01/06/2016 | 01/05/2017
...

I want to create a pivot table that displays the difference in months so either this:

A | 01/01/2001 | 01/02/2001 | 1
A | 01/01/2001 | 01/02/2008 | 85
etc...

or just the months:

A | 1
A | 85
etc...

Of course I could just use =DATEDIF(date1,date2,"M") which would work but I also want to add a slicer so I can filter A, B, C, ... this means the number of rows is not fixed and so I cannot just add that formula.

Is there a nice way to do this?

pivot-table excel-formula excel
1 Answers

You can calculate field inside the Pivot Table itself, and then you'll be able to use the slicers

Navigation:

PivotTable Tools>>Analyze>>Field Items & Sets>>Calculate Field

And then paste your datedif formula

=DATEDIF(Date1,Date2,"M")

Hope it helps!

user_103185040
Related