Hi, I have a pivot table with over 1000 lines of data. I need to filter on 2 dates ranges e.g. 05/01/13 to 10/05/13 and 20/10/13 to 12/12/13. I can only filter on the first date range. Is it possible to do 2 sets of date ranges in a pivot table?
Hi, I have a pivot table with over 1000 lines of data. I need to filter on 2 dates ranges e.g. 05/01/13 to 10/05/13 and 20/10/13 to 12/12/13. I can only filter on the first date range. Is it possible to do 2 sets of date ranges in a pivot table?
Instead of trying to select two sets of data in pivot. Keep 2 cells for date input and based on that let the formula to work with the source data to show only the selected data in pivot.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
sorry sixthsense, I do not understand what you mean by "keep 2 cells for date input" and what formula are you talking about?
Refer the attached file to know how to do it
Hi Sixthsense, that still only shows one date range in your example. I want to show 2 date ranges in the one pivot table. Thanks for helping.
I just shown you the method.. rest you have to develop
Thanks Sixthsense you gave me something to work on. I added a second date range to your formula and put the dates in K2 and K3 (see below). The dates in K2 and K3 are like yours as drop down lists. The formula in the GROUP column D identifies both date ranges which is great, however the pivot table won't recognise the two sets of date ranges.
=IF(COUNTIFS(A2,">="&$I$2,A2,"<="&$I$3)+(COUNTIFS(A2,">="&$K$2,A2,"<="&$K$3)),"Select","Deselect")
Pivot works fine now. copied the sheet but forgot to change data source, my mistake. Formula works great in conjunction with pivot. Thanks Sixthsense
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks