Pivot help - unsure if it's possible..

da_vide78

Board Regular
Joined
Mar 6, 2007
Messages
73
Can anyone tell me if it's possible to add 'start date' & End Date' parameters in a pivot table?


I have a load of data in a table and want to be able to show in Pivot form but with the ability to choose a start and end date.

How can this be done?

Or any other suggestions of being able to do this?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
The easy way is to add a formula to your underlying data that checks if the relevant date is between your start and end dates. You can then use this as a page field filtering on TRUE only. You need to refresh the table when the parameters change though.
 
Upvote 0
The easy way is to add a formula to your underlying data that checks if the relevant date is between your start and end dates. You can then use this as a page field filtering on TRUE only. You need to refresh the table when the parameters change though.

wicked - that sounds cool - how do i do that?
 
Upvote 0
Which bit? All of it?
 
Upvote 0
yea... kind of..

I have my table of data - Where would the formula sit - In the data sheet or the pivot sheet? What would the formula look like?
 
Upvote 0
It needs to be in the data sheet and the formula column needs to be included in the source data for the pivot table. (If you have already set up the pivot then the easiest way is to insert a new column within your current data - this will then be included in the pivot data)
Let's say you put the parameter dates in F1 and F2 on the pivot sheet (I'll call it Sheet2) and that your date field is in column A on the data sheet. Add your new column, give it a header in row 1 (say 'Filter') and then in row 2 enter:
Code:
=AND(A2>=Sheet2!$F$1,A2<=Sheet2!$F$2)
and fill down for all rows of data. This will give you a column of TRUE and FALSE values when you have entered your parameter dates. Now refresh your pivot table and you should see the Filter field appear in the Field List. Drag this to the Page area of the pivot table and filter it for TRUE only.
Now all you need to do is change your parameter dates and refresh the table and it should reflect the filtered data.
HTH
 
Upvote 0
Correct. You could also use code to automatically refresh the pivot when the parameter dates are changed.
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top