Pivot table with start and end date selection option.

[Sinner]

Hi,

I have a pivot table with some data. The data has a start date and an
end date.
Is there a way to control dates using pivot table by having a data
validation drop for start & end date and getting results in piovt
based on that.
Would appreciate if one can also drill the data between the date
ranges.


Thx.

 

[Debra Dalgleish]

You can add the date fields as filters in the page area, at the top of
the pivot table. Then, select a date from the dropdown list.

To extract data in a date range, you could add a column to the source
data, to calculate if the record's date are in the date range selected
in the data validation dropdowns. Add this field to the pivot table's
page area, and select TRUE from the dropdown list.
After you change the dates in the data validation cells, you can refresh
the pivot table, to see the data in that date range.

 

[Sinner]

You can add the date fields as filters in the page area, at the top of
the pivot table. Then, select a date from the dropdown list.

To extract data in a date range, you could add a column to the source
data, to calculate if the record's date are in the date range selected
in the data validation dropdowns. Add this field to the pivot table's
page area, and select TRUE from the dropdown list.
After you change the dates in the data validation cells, you can refresh
the pivot table, to see the data in that date range.

Hi Debra,

The data is as follows:

Date Quantity Item
01-Jan-08 5 A
02-Jan-08 8 A
01-Jan-08 9 B
03-Jan-08 5 C
01-Jan-08 2 D
04-Jan-08 6 E
04-Jan-08 5 A

----------------------------------------
Required:
Using Pivot table

Example:01

Start date:<selection from dropdown i.e. date column> 01-Jan-08
End date:<selection from dropdown i.e. date column> 03-Jan-08

A..............> 13
B..............> 09
C..............> 05
D..............> 02

Total: 29
------------------------------------------------------------------------------------------------
Example:02

Start date:<selection from dropdown i.e. date column> 04-Jan-08
End date:<selection from dropdown i.e. date column> 04-Jan-08

A..............> 05
E..............> 06

Total: 11

 

[Debra Dalgleish]

There's a sample file here that might help you get started:

http://www.contextures.com/excelfiles.html

Under PivotTables, look for 'PT0014 - Filter from Worksheet Date Range'

It uses programming to update the pivot table after the target dates are
changed. You could do the refresh manually instead, if you prefer.