2

I have a table on a worksheet that contains a Date column. I have created a Pivot Table/Chart on a new worksheet. I want to be able to filter the data in the chart being displayed by the last 14 days (Always relative to the current date).

I have the same problem as this user:filtering an Excel pivot table to show the last 24 hours data

Except I can't add a column to the source Data and I need it within 14 days instead of 24 hours. There must be built-in way to do this or can I do this in VBA?

Thanks

Community
  • 1
  • 1
John
  • 147
  • 3
  • 11

1 Answers1

2

You can filter by date, of course, but without VBA I think it has to be manual. Here's a VBA routine that sets it to the last 14 days, including today:

Sub FilterPivotByDate()
Dim pvt As Excel.PivotTable
Dim DaysToShow As Long
Dim DateString As String

Set pvt = ActiveSheet.PivotTables(1)
DaysToShow = 114
DateString = Format(Date - (DaysToShow - 1), "m/d/yyyy")
With pvt.PivotFields("date")
    .ClearAllFilters
    .PivotFilters.Add Type:=xlAfterOrEqualTo, Value1:=DateString
End With
End Sub
Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
  • You're welcome. Thanks for the chance to write fun code. – Doug Glancy Mar 12 '14 at 04:05
  • I'm not sure if you can help me with this but it cropped up afterwards. In order to use your solution, I need to add the Date as an Axis (Category) - Is there a way to do the same thing if it were a report filter instead? The problem is that it's dividing my results by month – John Mar 12 '14 at 07:22
  • Is a report filter your first choice, or do want to do it because it will solve your month grouping issue? To do it in a report filter I think the only way is to loop through each item and set its `Visible` property. I don't have any more time now, but will check back later. – Doug Glancy Mar 12 '14 at 14:01
  • I don't need to filter the dates in any way other than the past 14 days, although my design changed - so the dates don't always need to be relative to the current date. I added 2 calendar controls to get those between dates. So I need to reference that Axis(Category) of my Date field to filter between dates, but not have it apply it's default filtering functionality where it's splitting up my values by month. – John Mar 12 '14 at 23:55
  • I can make dates in a pivot table act like what you're seeing as the default by choosing month in the Group dialog. Conversely, right-clicking the dates and choosing UnGroup makes both the pivot table and pivot chart show each date separately. – Doug Glancy Mar 14 '14 at 03:00
  • Thanks again for the help. It turned out I just needed to group by seconds to avoid it breaking up my results by month. – John Mar 17 '14 at 09:32