Excel Pivot table must filter based on the date value in a cell

Copper Contributor


Dear Team,
I want the Pivot table to automatically filter Settled Date based on the Target Date.
I expect Settled Date to be greater than or equal to the Target date value in J2.
Expected result:


Thank you,
Attached you will find editable sample file link:
Filter Date with Specific value in Cell.xlsx

2 Replies


Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("J2"), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        With Me.PivotTables("PivotTable1").PivotFields("Settled Date")
            If IsDate(Range("J2").Value) Then
                .PivotFilters.Add2 Type:=xlAfterOrEqualTo, _
                    Value1:=Format(Range("J2").Value, "Short Date")
            End If
        End With
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open the workbook.


As variant creating PivotTable add data to data model plus add target data to data model as another table.

Create DAX measure

Amt:=VAR target=VALUES(Table1[Target Date] )
       IF(Range[Settled Date] >= target,  Range[Amt ₹], BLANK() ) ))