How to add a Calendar filter for my Pivot Table?

by

I am building a pivot table in excel for work. Is there a way where I can build a calendar slicer, where someone can click on (for example) the 30th of September, and update the pivot table?

The columns I have are:

  1. Product
  2. City
  3. Start date
  4. End date

Disclaimer: I am not looking for slicers. These are not user friendly since I have 2 date columns Start and End dates

I tried slicers, but these werent good enough. If I had a product sell only 1 day of August for example, it would show as all the month.

Try using Excel’s ActiveX controls & some VBA code. Here’s a solution to create a calendar control that updates a pivot table based on the selected date:

1. Inserting a Calendar Control:

  1. Go to the worksheet where you want the calendar control.
  2. Click on the Developer tab. If you don’t see the Developer tab, you’ll need to enable it first.
  3. Click on Insert in the Controls group.
  4. From the ActiveX Controls section, select More Controls (the tool icon).
  5. From the list, select Microsoft Date and Time Picker Control and click OK.
  6. Draw the control on your worksheet.

2. Add VBA Code to Update the Pivot Table:

  1. Right-click on the calendar control and select Properties. Change the Name property to something like DatePicker.
  2. Close the properties window.
  3. Right-click on the calendar control again and select View Code.
  4. In the VBA editor, you’ll see a code window for the calendar control. Enter the following code:
Private Sub DatePicker_Change()
    Dim pt As PivotTable
    Dim startDateField As PivotField
    Dim endDateField As PivotField
    Dim selectedDate As Date

    ' Set the pivot table
    Set pt = ThisWorkbook.Worksheets("YourPivotSheetName").PivotTables("YourPivotTableName")
    
    ' Set the start and end date fields
    Set startDateField = pt.PivotFields("Start date")
    Set endDateField = pt.PivotFields("End date")
    
    ' Get the selected date from the calendar control
    selectedDate = Me.DatePicker.Value
    
    ' Clear any existing date filters
    startDateField.ClearAllFilters
    endDateField.ClearAllFilters
    
    ' Apply the date filter
    startDateField.PivotFilters.Add Type:=xlDateAfter, Value1:=selectedDate - 1
    endDateField.PivotFilters.Add Type:=xlDateBefore, Value2:=selectedDate + 1
End Sub

Replace YourPivotSheetName with the actual name of the worksheet containing the pivot table and YourPivotTableName with the name of your pivot table.

3. Test the Calendar Control:

  1. Return to Excel and select a date from the calendar control.
  2. The pivot table should update to show only the data where the selected date falls between the start and end dates.

This solution allows users to select a date from the calendar control, and the pivot table will update to show only the products that were available on that date.

Leave a Comment