Filter dates on pivot table

[YasserKhalil]

I am trying the following code so as to select only the dates before specific date. The specific date is in cell Q2. So I need to select any date which is less or equal than my date in cell Q2

Code: Select all

Sub LoopThroughPivotItems()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim filterDate As Date
    Set pt = ActiveSheet.PivotTables("PivotTable2")
    Set pf = pt.PivotFields("ÇáÊÇÑíÎ")
    pf.ClearAllFilters
    filterDate = CDate(ActiveSheet.Range("Q2").Value)
    For Each pi In pf.PivotItems
        If CLng(DateValue(CDate(pi.Name))) > CLng(DateValue(filterDate)) Then
            pi.Visible = False
        Else
            pi.Visible = True
        End If
    Next pi
End Sub

The code selects only one date which is 01/01/2023 while a date such as 02/01/2023 and 03/01/2023 not selected!!

[YasserKhalil]

Solved by swapping day and month as for pi.Name
Here's the working code

Code: Select all

Sub Loop_Through_PivotItems()
    Dim myDate, pt As PivotTable, pf As PivotField, pi As PivotItem, formattedDate As Date, sDate As String
    sDate = Join(Array(Chr(199), Chr(225), Chr(202), Chr(199), Chr(209), Chr(237), Chr(206)), Empty)
    Set pt = ActiveSheet.PivotTables("PivotTable2")
    Set pf = pt.PivotFields(sDate)
    pf.ClearAllFilters
    myDate = ActiveSheet.Range("Q2").Value
    For Each pi In pf.PivotItems
        formattedDate = CLng(ConvertToDate(pi.Name))
        If CLng(formattedDate) <= myDate Then
            pi.Visible = True
        Else
            pi.Visible = False
        End If
    Next pi
End Sub

Function ConvertToDate(ByVal inputString As String) As Date
    Dim dateParts() As String, iDay As Long, iMonth As Long, iYear As Long
    dateParts = Split(inputString, "/")
    iDay = CInt(dateParts(1))
    iMonth = CInt(dateParts(0))
    iYear = CInt(dateParts(2))
    ConvertToDate = DateSerial(iYear, iMonth, iDay)
End Function