+ Reply to Thread
Results 1 to 4 of 4

Custom dynamic date filters in pivot tables

  1. #1
    Registered User
    Join Date
    11-27-2019
    Location
    Australia
    MS-Off Ver
    Microsoft Office 365 Pro Plus
    Posts
    2

    Custom dynamic date filters in pivot tables

    I am very new to pivot tables but some searching through forums still didn't give me any answers.

    Capture.JPG

    My spreadsheet has pivot tables that sort through a data dump from our asset management software that tracks reported defects. My goal is to have this pivot table only display rows with a date first reported that is two months or older. However using the predefined Date Filters I can only show values from last month, which does not show anything earlier or I can select Before... which only works with specified dates and returns a "This isn't a valid date" error if I try and use standard Excel logic like =EDATE(TODAY(), -2)

    Capture.JPG

    Hopefully I am just missing something simple but any help would be great

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,163

    Re: Custom dynamic date filters in pivot tables

    Hi Joe,

    How about using an Advanced Filter on your data and doing a Pivot of the result? Or, using VBA might be another method. See the attached where I've used your formula as a criteria for an Advanced Filter. I made up my own data....

    PT using Advanced Filter 2 months ago.xlsm
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    11-27-2019
    Location
    Australia
    MS-Off Ver
    Microsoft Office 365 Pro Plus
    Posts
    2

    Re: Custom dynamic date filters in pivot tables

    Quote Originally Posted by MarvinP View Post
    Hi Joe,

    How about using an Advanced Filter on your data and doing a Pivot of the result? Or, using VBA might be another method. See the attached where I've used your formula as a criteria for an Advanced Filter. I made up my own data....

    Attachment 651819
    This is where I start to run into issues, is there a way to have an advanced filter run automatically? The current process is to copy several dumps of raw data from our asset management software onto a separate work sheet and simply refresh all pivot tables (this is a rather large report with ~40 tables) and it has to be run within an hour of the meeting to get the most current data. Plus this is handled by our admin assistant who is not overly skilled with Excel so I am trying to make things as simple and automated as possible to help relieve some of the stress off them.

    The next issue lies with VBA, it is currently blocked by admin and will be a very drawn out process to get the approval I need to use it which I am not overly against at this stage, as long as it saves hassle in the long run. The next issue is remembering back to how to actually use it

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,163

    Re: Custom dynamic date filters in pivot tables

    OK Joe,

    To deal with a variable amount of original data you need DNRs which is Dynamic Named Ranges. They will expand or contract based on the amount of data downloaded. Then the Criteria area looks to be static. The Pivot Table just needs to be refreshed based on the output which is another DNR. No VBA needed just a little training on how to do Advanced Filters and then Refresh Pivots.

    https://www.ozgrid.com/Excel/DynamicRanges.htm

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Dynamic Date ranges in pivot tables
    By Tibbs in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-24-2018, 10:30 AM
  2. Macro to update pivot table filters from multiple pivot tables of different data sources
    By groblerdn85 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-24-2017, 05:10 AM
  3. Dynamic Filters for PowerPivot Sourced Pivot Tables
    By nnazarian in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-13-2015, 02:06 PM
  4. Excel 2007 : Pivot tables filters
    By Squevil in forum Excel General
    Replies: 1
    Last Post: 11-04-2011, 10:30 AM
  5. Pivot tables - Multiple filters across 2 tables
    By shoesterix in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-23-2011, 05:02 AM
  6. Pivot tables with dynamic filters
    By Scotty81 in forum Excel General
    Replies: 0
    Last Post: 05-08-2009, 11:04 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1