VBA Pivot Filter - Date Range

Question

Looking to filter a pivot table to be within a date range. The date filter is at the top of the pivot, with the main table having 3 columns. I have a picture with actual examples but can't upload here.

If I enter a date range of 1st Feb. 2018 - 1st March 2018 the filter works perfectly. If I enter a date range of 1st Feb. 2018 - 28th Feb 2018 the filter misses out the 3rd Feb - 9th Feb, picking back up again for the remainder of the data from the 10th Feb.

Different date ranges produce variants of this behavior.

From my research online this type of filtering in VBA has a bug of some sort where the code reads the data in US date format, regardless of Excel settings & the data itself (hence the formatting code, without it causes a mismatch error). I've seen a couple of workarounds online such as using CLng but the method below is the closest I've got.

• The pivot table itself is on a worksheet called "Pivots". Columns A-C, Date in cell B2, main table headers in row 4.
• The date range is on a worksheet called "Paretos", cell refs below.
• The table I'm working on here is PivotTable1

---

Sub FilterPivotDates()

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.EnableEvents = False

Dim ws As Worksheet, ws2 As Worksheet, pt As PivotTable, pf As PivotField, PI As PivotItem
Dim FromDate As Date, ToDate As Date

Set ws = ThisWorkbook.Worksheets("Pivots")
Set ws2 = ThisWorkbook.Worksheets("Paretos")

FromDate = ws2.Range("B1").Value
ToDate = ws2.Range("E1").Value

pivno = 1
MCCol = 25

Set pt = ws.PivotTables("PivotTable" & pivno)
Set pf = pt.PivotFields("Date")

'On Error Resume Next

Do While pivno < 2 '25
    Set pt = ws.PivotTables("PivotTable" & pivno)
    Set pf = pt.PivotFields("Date")
    pt.PivotFields("Date").ClearAllFilters
    With pf
        For Each PI In pf.PivotItems
            If PI.Value >= Format(FromDate, "M/D/YYYY") And PI.Value <= Format(ToDate, "M/D/YYYY") Then PI.Visible = True Else PI.Visible = False
        Next
    End With

pivno = pivno + 1

Loop

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Stepping through using msgbox commands it seems the missing dates are failing on one of the date checks, so the AND function removes the entry. I can't work out whats going on.

Using Excel 2016

Answer 1

PivotItems.Value property returns a String value. Format function also returns a String (Variant) value.

Therefore, when you perform a comparison of PI.Value vs FromDate / ToDate, it seems that your code performs a TEXT comparison, not DATE comparison. In text comparison, some dates are rightfully "out of range".

Reproduce the simple table in picture below (ensure that all fields are set into text format before entering values), and you'll see that it can reproduce the same "buggy" behaviour as you mentioned.

Solution is, probably, to somehow convert the values you compare, back into date format.

Answer 2

Excel stores Dates as numeric values, even though you might change the format of it from "mm/dd/yyyy" to "dd-mmm-yy" or to a monthly name like "mmmm", it really doesn’t change the way Excel stored the value inside, which is Numeric.

For instance, take 01-Feb-2018, if you copy >> Paste Special (Values only) to the neighbor cell, you’ll get 43132.

In your case, the best way is to compare the numeric values of the dates.

In your code, replace your line:

If Pi.Value >= Format(FromDate, "M/D/YYYY") And Pi.Value <= Format(ToDate, "M/D/YYYY") Then ...

With:

If Pi.Value >= CDbl(FromDate) And Pi.Value <= CDbl(ToDate) Then ...

You could optimize your Do While loop a little:

Do While pivno < 2 '25
    Set pt = ws.PivotTables("PivotTable" & pivno)
    Set pf = pt.PivotFields("Date")

    With pf
        .ClearAllFilters

        For Each pi In .PivotItems
            ' since you already used .ClearAllFilters, you don't need to use Visible = True,
            ' only hide the ones which are not within your desired dates range
            If Not (pi.Value >= CDbl(FromDate) And pi.Value <= CDbl(ToDate)) Then pi.Visible = False
        Next
    End With

    pivno = pivno + 1
Loop

Answer 3

Ok it appears I've found a workaround.

The source data used to create the pivot needs to be raw Excel numbers, 43108 or whatever rather than the date.

When this is done using CDbl(FromDate) & such appears to work.

Just an attempt to clarify, the issue stems from the pivot item name (or caption, or value etc.), when it is a date, not being able to be formatted or set or processed to anything other than a US date format. Trying to match any data to it doesn't seem to work, only changing the raw data as above & converting any filter criteria within the code using CDbl seems to get me anywhere.