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.

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 wrote:
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
Contextures
http://www.contextures.com/tiptech.html

On Feb 22, 3:52*am, Debra Dalgleish wrote:
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 wrote:
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
Contextureshttp://www.contextures.com/tiptech.html

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

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.

Sinner wrote:
On Feb 22, 3:52 am, Debra Dalgleish wrote:

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 wrote:

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
Contextureshttp://www.contextures.com/tiptech.html


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
Contextures
http://www.contextures.com/tiptech.html