How to set date filter in pivot table.

[Jagdev Singh]

Hi Experts,

Could you please let me know if it is possible to set automatic filter on the date column of the pivot table. I want to filter the data in the pivot table with following condition (today's date + 60 days) in the date column of the table.

Example today is 24th November'14 and I want pivot to display the data till the date 24th January, 2015 without manual intervention.

Is there a way to do it?

Please find the sample file attached.

Regards,
Jaggi

 

[prasaddn]

Hi Jagdev,

I hope you are fine with VBA solution.

if yes, check the code in the thread:
http://chandoo.org/forum/threads/ho...-from-cell-values-with-vba.19261/#post-116218

you may need to change slightly to fit your requirements.

Regards,
Prasad DN

 

[jeffreyweir]

You can also add a new column to your source data, and filter on that.

Note that it's best to turn PivotTable Data Sources into Excel Tables, so that if you add new data, it always shows up in the PivotTable on refresh.

See attached. This will be covered in my forthcoming book, Excel for Superheroes and Evil Geniuses which will have an entire section devoted to PivotTables

 

[Jagdev Singh]

Hi Jeff and Prasad

Thanks for the code. It works fine.

Regards,
Jaggi