Pivot Table - Count only within date range

[Guest]

Hi,

I want the Pivot to give me separate counts for all records from dates
01/01/02 - 31/10/06 and 1/11/06-31/12/20. (Basically tell me how many
records have a date that has passed since the end of October and how many are
still outstanding).

There must be a simple way to do this but when the field is in the ROW or
COLUMN heading it will let me group and show data during a specific date
range but only once - I cannot get the fields to display twice, each only
showing one of the above ranges.

If I put the field into the DATA area, it will not let me sort at all!

I have also tried a Formula Column entering this formula
=COUNTIF(Expire"<31/10/06") and it says there is an error with the formula.
(Expire being the field with the information I am using).

I am hoping Pivot Tables allow you to do this - or perhaps it won't let you
show information from two different date ranges???

Thanks!

 

[Debra Dalgleish]

In the workbook, type your cutoff date, 1/11/06, in a cell
Name that cell CheckDate
There are instructions here for naming a range:

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

Then, in the pivot table's source table, add a column with the heading
"Status".
In the first row of that column, enter a formula that checks the
record's date. For example, with the date in column A:

=IF(A2<CheckDate,"Old","Active")

Copy the formula down to the last row of data.
Refresh the pivot table, and add that field to the column area.
Add the date field to the data area, as count of Date.