Pivot Table count dates older than x days


New Member
Mar 31, 2005

I have a table with columns...

- date a ticket was opened
- format = dd/mm/yyyy

- name of a group ticket was assigned to
- format = [text string]

- flags whether the ticket status is still open
- format = true/false

I currently have a pivot table set up, where

Columns =
- Opened (Year)
- Opened (Month)
- Opened

Rows =
- Group
- Active

Values =
- Count of Opened

The rows in the pivot table have a filter for "Active" where only "TRUE" values are selected.

Table works fine, but I would like to only count active records that were opened more than 10 days ago (to report aged record totals outstanding against support groups)

Note: I am aware of the Date Filter "is before", however this requires a specific date, whereas I would like older than 10 days from "today".

Note: the data is being cut/pasted from another source into a sheet on the spreadsheet, to keep things simple I do not want to include a helper column where data is being dumped in the event it get wiped or corrupted.

Note: I have PowerPivot activated, but have little experience with it and I am familiar with using VBA in excel.

I'm guessing, the options are;

1 - helper column that creates a true/false test of opened date older than today+10 (do not wish to implement this solution as stated above).

2 - some alternative to the "is before" data filter that I have yet to discover(?)

3 - a VBA function that applies a filter to the pivot table, please note the data will be used for other charts and stuff so cannot be directly manipulated.

4 - a powerpivot measure/calculated column that filters the data as required(?) - as I have no experience with powerpivot I would ask that any solution is dumbed down into novice level steps of implementation.

Your assistance with this challenge is most appreciated.


Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Watch MrExcel Video

Forum statistics

Latest member