Change pivot page filter based upon two cell values (dates from and to)

[Victor70]

Dear experts,

I saw solutions to update the pivot page filter based upon a cell value via a macro (see the link).
http://www.excelforum.com/excel-prog...ell-value.html

Is there a way to apply a from-to criteria for the pivot page filter to get the data for specific time interval? I was unable to modify the referenced code being unsure if pivot page filter supports complex criteria at all.

Thanks a lot for any thoughts.

This duplicates the same question at http://www.excelforum.com/excel-gen...d-upon-two-cell-values-dates-from-and-to.html No responses there so far...

 

[Jerry Sullivan]

It's no bother, Arvind

Try adding the line shown below then running the macro.
It should print some lines to the VB Editor's Immediate Window that will help us fix the mismatch.

With pvtField
        .Parent.ManualUpdate = True
        For i = 1 To .PivotItems.Count
            dtTemp = .PivotItems(i)
            bTemp = (dtTemp >= dtFrom) And _
                (dtTemp <= dtTo)
          [B] [COLOR="#0000CD"] Debug.Print "Is: " & dtTemp & " between " & dtFrom & " and " & dtTo _
                & "? Result: " & bTemp[/COLOR][/B]

The output should show what's being compared as the code looks for the first PivotItem that falls in the Date Range
(Yours might have different date formats)

Is: 11/24/2011 between 5/1/2012 and 5/31/2012? Result: False
Is: 11/25/2011 between 5/1/2012 and 5/31/2012? Result: False
.... <snipped many lines>
Is: 4/28/2012 between 5/1/2012 and 5/31/2012? Result: False
Is: 5/1/2012 between 5/1/2012 and 5/31/2012? Result: True

Don't post the entire output, just post the few first lines and last lines as I have above.

 

[arvindkrkaushik]

Hi Jerry,

I found the problem....I crated anoter workbook and just ran this macro for Created Date1 Pivot page field it worked fine for me and showing the correct dates this time but I add another pivot "Joined on 1" BUT This is not working fine for this filed and showing incorrect results and for others as well like "HAT Test - Schedule1"......this is because Created Date1 field has all dates with no blank dates but for others Like Joined on 1, "HAT Test - Schedule1" there are blank dates.... So I therefore request you to please run your macro specifically for "Joined On1"....Field...

Note: Also if you right click on "HAT Test - Schedule1" date field=>Field Settings this will not show the option to change the date format however for Created Date1 filed it will allow you to change the date format as there are no blank dates in created date1 field, for other pivots like "Joined on1" there are blank rows this is why this not allowing us to change the date format.

Please look into this, hoping for resolution for this problem, waiting for your response on this.

Thanks,
Arvind Kaushik

Jerry,

I have already re-downloaded the file you sent me but still I am facing the same problem. Also I am using Execel Version 2007, And My regional setting date format is : dd-mmm-yy and which I can not change as other applications are using this format. So please suggest me other way so that this macro could work fine for me.

Note: I also ran your macro by debuging step by step and this is also comparing the correct date formats but still pivots are not showing correct values.

Sorry for bothering you again and again.

Thanks,
Arvind Kaushik

 

[arvindkrkaushik]

Jerry,

This is showing: I am also sending you my file once again...The fresh file.
Is: 09-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 12-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 16-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 19-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 23-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 25-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 27-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 30-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 02-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 06-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 09-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 13-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 17-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 18-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 20-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 21-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 24-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 27-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 01-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 05-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 09-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 12-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 15-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 19-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 22-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 26-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 29-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 02-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 05-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 09-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 12-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 16-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 17-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 19-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 20-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 23-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 24-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 26-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 27-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 28-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 30-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 02-May-2012 between 01-May-2012 and 12-May-2012? Result: True
Is: 24-Nov-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 25-Nov-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 26-Nov-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 28-Nov-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 29-Nov-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 30-Nov-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 01-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 02-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 03-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 05-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 06-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 07-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 08-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 09-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 10-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 11-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 12-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 13-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 14-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 15-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 16-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 17-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 19-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 20-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 21-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 22-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 23-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 24-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 25-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 26-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 27-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 28-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 29-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 30-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 31-Dec-2011 between 01-May-2012 and 12-May-2012? Result: False
Is: 02-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 03-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 04-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 05-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 06-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 07-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 08-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 09-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 10-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 11-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 12-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 13-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 14-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 15-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 16-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 17-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 18-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 19-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 20-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 21-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 22-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 23-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 24-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 25-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 26-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 27-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 28-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 29-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 30-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 31-Jan-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 01-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 02-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 03-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 04-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 05-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 06-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 07-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 08-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 09-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 10-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 11-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 12-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 13-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 14-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 15-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 16-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 17-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 18-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 19-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 20-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 21-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 22-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 23-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 24-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 25-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 26-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 27-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 28-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 29-Feb-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 01-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 02-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 03-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 04-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 05-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 06-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 07-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 08-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 09-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 10-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 11-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 12-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 13-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 14-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 15-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 16-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 17-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 18-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 19-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 20-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 21-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 22-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 23-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 24-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 25-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 26-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 27-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 28-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 29-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 30-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 31-Mar-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 01-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 02-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 03-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 04-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 05-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 06-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 07-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 08-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 09-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 10-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 11-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 12-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 13-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 14-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 15-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 16-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 17-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 18-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 19-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 20-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 21-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 22-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 23-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 24-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 25-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 26-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 27-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 28-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 29-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 30-Apr-2012 between 01-May-2012 and 12-May-2012? Result: False
Is: 01-May-2012 between 01-May-2012 and 12-May-2012? Result: True
lse

Thanks,
Arvind

 

[arvindkrkaushik]

Jerry,

I sent you my fresh file to your e-mail id:
Please resolve my problem....

Thanks,
Arvind

 

[Jerry Sullivan]

Arvind, Please edit your post and remove my email address.

 

[arvindkrkaushik]

Sorry Jerry!!!!

i don't know how to edit the post...pls suggest how to eid this.

Thanks,
Arvind

 

[Jerry Sullivan]

Sorry Jerry!!!!

i don't know how to edit the post...pls suggest how to eid this.

You can only edit your post within 10 minutes of writing it.
After that, a Moderator needs to access. I've requested that a Moderator help with that.

I will foward all my Spam to you!

 

[arvindkrkaushik]

My apologies Jerry, If I hurt you.

this will not happen in future....

Thanks,
Arvind

 

[Jerry Sullivan]

No worries- I understand.

I'm wrapping up for the night here.
I'll take a look at this tomorrow.

 

[Jerry Sullivan]

Arvind, I was able to replicate your findings by changing my Regional Settings to match yours.

It appears that there is a corruption problem with the Joined On1 field which causes .PivotItem(i).Visible to return an Error for all values of i.

I was able to remedy this by:
1. Renaming "Joined On1" to "Joined On Renamed" in the data source header.
2. Refreshing the Pivot Table
3. Placing the New "Joined On Renamed" field in the Report Filter area.

Give this a try and see if this works for you.