Filter excel 2010 pivot table for dates after or equal to today

Question

Hi,

I have a pivot table which looks at the number of appointments different staff members have had/ have coming up.  I have dates in the row field, names in the column field and a count of staff id in the data/value field.

I know there are dynamic date filters; but they don't meet my requirements here.  I'd like to dynamically filter the dates to show today's date and any future date.  I've tried the custom filter and have discovered that I can't use =today() or =(now) as a criterion, unless I'm missing something.  I've also tried referring to a cell containing the =today() formula as my criteria; but it seems I can't do that either.

I know I could add a field to the source data which checks if the date matches my criteria and then I could add that as a page/report filter field; but I don't want to confuse the users of the workbook.

Is it possible to create a custom filter from the date field to satisfy my requirements?

Many thanks for your help.

Karen

Answer 1

I found that the date filter built into Excel 2010 Pivot Tables does not appear to adjust automatically using the method suggested.  Is there a way to have it automatically adjust for today and after so this does not need to be done manually each day on dozens of pivot tables?

Answer 2

you should not be changing the filter every day that should take care automatically.

Answer 3

Hi Chausty,

That's exactly the same problem I'm still having.  I hope someone can help us.

Thanks for your reply.

Karen

Answer 4

Hi,

Thanks very much for that.  It makes sense. 

Will I need to change the filter on a daily basis though as yesterday's date will change?

I'm looking for a way to set up the filter so no matter what the current date is it still works.

Thansk for your prompt reply.  It is appreciated.

Kind regards

Karen

Answer 5

Hi,

At row label, if you use the drop down then as you said there is date filter. In Date Filter use "After" and there choose yesterday's date. It will show the list which is equal to for dates which are after or equal to today.