Comparing Date Range Excel VBA

Question

UPDATED WITH ANSWER: I found an answer to my question. I'll place a simplified version below the original question here, just to make it easy, with a more detailed version below as the accepted answer.

I'm writing a VBA function to filter a pivot table by a date range entered by the user. For some reason, it is not returning the results expected.

The dates being compared are in two different date formats. The user-entered dates are in mmmm yyyy format (October 2013). When this value is pulled into the macro for comparison, it is translated correctly as 10/1/2013. The pivot table dates are in mmm yy format (Oct 13). When I call on this date with the code PivotItem.Value it seems to be translating the date as a string "Oct 13."

I can't quite figure out what the macro is doing, as it behaves somewhat erratically. If I run it for October 2011 to October 2013, it returns all months from Jan to September for every year, 2008, 2009, 2010, etc. If I run it for June 2013 to October 2013, it returns June to September for every year. Furthermore, in each example, the macro continues to run past the maximum range of data in the pivot table and gets an error. When I debug, the macro is trying to set the visibility to 'true' for a date that doesn't even exist in the pivot table (IE for Jan 2014 when the data only goes through Oct 2013). No idea why that's happening.

Below is the code. Any insight would be greatly appreciated.

UPDATE:

So the problem is definitely the date format. If I change the field settings in the pivot table to the date format mm/dd/yyyy (10/1/2013), then the macro works exactly as expected. This would be a simple fix to the problem except that the table is feeding a chart seen in the user dashboard, which I would really like to be in the format mmm yy, since it looks much cleaner. Is there a simple way to convert the format to mm/dd/yyyy inside the macro for the comparison, then back to the desired format once complete?

And I would still like to understand why a different date format is returning such different results, when the raw data being compared is the same, and both are formatted as dates, not like date vs text or something.

Sub filterPivotDate(pt As PivotTable, strtDate As Date, endDate As Date)

Dim pf As PivotField
Dim pi As PivotItem

'Clear current date filter
Set pf = pt.PivotFields("Date")
pf.ClearAllFilters

'Set new date filter
For Each pi In pf.PivotItems
    If pi.Value >= strtDate And pi.Value <= endDate Then
        pi.Visible = True
    Else
        pi.Visible = False
    End If
Next pi

end sub

ANSWER UPDATE: I replaced the loop I was using to set the filter with the following line of code:

pf.PivotFilters.Add Type:=xlDateBetween, Value1:=strtDate, Value2:=endDate

This solved the issue I was having with the date format. More information in the accepted answer below, as well as at this website

Answer 1

I might be wrong, but one of your < characters looks backwards- don't you want start dates Greater than the variable?

Answer 2

You could try converting the string date value first:

Dim dateValue as Date

'Set new date filter on all pivot tables
For Each pt In ws.PivotTables
    Set pf = pt.PivotFields("Date")

    For Each pi In pf.PivotItems

        If IsDate(pi.Value) Then
            dateValue = CDate(pi.Value)
            If dateValue < strtDate Or dateValue > endDate Then
                pi.Visible = False
            Else
                pi.Visible = True
            End If
        End If
    Next pi

Next pt

'call this function
Function IsDate(byval thisDateString as String) As Boolean
    On Error Goto ErrorHandler

    Dim d as Date
    d = CDate(thisDateString)
    IsDate = true
    Exit Function

ErrorHandler:
    IsDate = false
End Function

Answer 3

I found some additional information about setting pivot table filters via VBA that improves upon the original code and solves the issue I was having. I decided to post it, as I found the information extremely helpful.

There are a lot of different filters you can set with simple, one line commands, rather than complicated loops which parse all the data and set visibility manually based on a condition as I was doing before. Below is the updated code, which also solved the issue I was having with the date formatting.

There is some really useful information at this link to the globaliconnect website about different pivot table filter settings you can set using VBA.

I'm still not certain why the date was behaving weird before when doing the comparison in a loop, but dates are just kinda like that I guess...

Sub filterPivotDate(pt As PivotTable, strtDate As Date, endDate As Date)
   Dim pf As PivotField
   Application.ScreenUpdating = False

   'Clear date filter and set new filter
   Set pf = pt.PivotFields("Date")
   pf.ClearAllFilters

   'I REPLACED THE LOOP WITH A SINGLE LINE TO SET A FILTER BETWEEN TWO DATES
   pf.PivotFilters.Add Type:=xlDateBetween, Value1:=strtDate, Value2:=endDate

   Application.ScreenUpdating = True
End Sub