A date with PivotItems

Howdy, folks. Jeff here again. I’ve been sharpening up some code to manually filter a PivotField based on an external list in a range outside the PivotTable. It works blazingly fast. Unless there’s dates as well as non-dates in my PivotField. In which case it errors out.

Try this:
Put “Pivot” in A1
Put =TODAY() in A2
Put =VALUE(TODAY()) in A3

Now make a PivotTable out of that data.

Pivot_DDOE

Now put this code into the VBE and step through it:

Sub WhatThe()

Dim pf As PivotField
Set pf = ActiveSheet.PivotTables(1).PivotFields(1)
With pf
.NumberFormat = "d/mm/yyyy"
.PivotItems(1).Visible = True
.PivotItems(2).Visible = True
.NumberFormat = "General"
.PivotItems(1).Visible = True
.PivotItems(2).Visible = True
End With
End Sub

If the same thing happens to you as happens to me, you will either be speaking aloud the title of this routine, or you will be speaking aloud this:
Unable to get the PivotItems property of the PivotField class.

Go type these in the immediate pane:

? .PivotItems(1).name
41588
? .PivotItems(1).visible
True
? .PivotItems(2).name
10/11/2013
? .PivotItems(2).visible
Error 2042

What the…?

Now try these:

? ActiveSheet.PivotTables(1).PivotFields(1).numberformat
General
ActiveSheet.PivotTables(1).PivotFields(1).numberformat = "d/mm/yyyy"
? .PivotItems(2).name
10/11/2013
? .PivotItems(2).visible
True

So it seems can’t do certain stuff to a PivotItem if that PivotItem is a date but your PivotField number format is set to General.

That’s weird.

Equally weird, try this:
Select the PivotTable, and record a macro as you change it’s number format back to General.

Sub WhatThe_Part2()
ActiveSheet.PivotTables(“PivotTable14”).PivotFields(“Pivot”).Name = “General”
End Sub

What the …? Change the PivotField Number Format, and you get a macro that tells you that you changed the PivotField name!

So what happens if you run that macro? Well, it changes the name of the PivotField:
PivotField_20131110

It does nothing to the number format.

Strangely enough, I found some info on this problem at one of my most revisited blogposts that I had somehow missed: Jon Peltier’s Referencing Pivot Table Ranges in VBA

Stranger still, the answer was by Jon Peltier back in 2009 in relation to a question asked by….wait for it…me. Don’t know how I missed that. Must have been sleeping.
So I’ve come across this problem before, found an answer, completely forgotten about it, and then wasted 2 days trying to work out what the problem was, purely so I could Google my own answered question.

I’m going to read through all 238 ( and counting) comments in that thread and see what else Jon has told me over the years I’ve been learning VBA.
There’s also something on this at stackoverflow

Jeff

–edit–
Jon’s method was to loop through the pivot items, and compare the pivot item caption to what he was looking for:


For Each pi In pt.PivotFields("Order Month/Year").PivotItems
If pi.Caption = Format(TheDate, "m/dd/yyyy") Then
' or If DateValue(pi.Caption) = TheDate Then
'' FOUND IT
End If
Next

But now I know it’s probably easier just to change the format of the PivotField.

—edit 30 May 2014 —
Prompted by Jerry Sullivan’s comment I found that this was only an issue for non-US regional settings, and that this issue is now fixed in Excel 2013. (Thank you, Microsoft. But why the heck didn’t you tell me you’d fixed it?)

According to IronyAaron in the comments at this thread:

When VBA recognizes the dates in the pivot cache, it reads the US version after parsing although the item is read as a locally formatted string. This therefore causes VBA to fail when recognizing Date variables.

Bummer! So write some code that filters PivotItems, and you might find that non-US users have issues, unless they change their regional settings in Windows to US beforehand. Good luck with that.

— edit over —

17 thoughts on “A date with PivotItems

  1. Jeff, I can sympathise entirely. I keep pretty good notes on various subjects, using Word and some handy bookmarks and hyperlinks to jump around from the contents page. But only those readers who have been through the experience will know how this feels. To spend a couple of great days battling through some weird scenario, emerge triumphant on the other side, battered but wiser. You open up your notes file ready to commit your latest gem to the section entitled ‘never need to know this again’, only to find you`ve been there, seen it, cracked it, and thoroughly documented it – maybe not even six months ago. What is the word for when elation instantly changes to an embarrassed despondency. A mix of triumph and disaster. Triaster? Disumph?

  2. Triaster sounds better!

    Actually, I’ve just realized that neither Jon’s approach nor mine will solve the problem. In the code snippet from Jon’s site, he uses this to identify the correct PivotItem. But having identified it, you still can’t hide it if it’s a date and your PivotField date format is NOT a date.

    And as for my suggestion – change the format of the PivotField – you can only do that if your entire PivotField contains items that are all formatted the same way. It is in my very basic example. But if you add some text in cell A3, then you can no longer set the PivotField format…attempting to do so generates an error.

    So my blazingly fast filtering routine will have to simply warn the user that dates will not be filtered. Stink!

  3. @Krishna: That formats the underlying cells, but not the Pivot. Furthermore, as per comment above, if you have dates mixed in with say text then the PivotField doesn’t actually have a format (not even General) so any attempts to change it result in a run-time error.

  4. Jeff, Thanks for shedding more light on the vexing topic of manually filtering by dates in PivotTables.

    You noted,
    In the code snippet from Jon’s site, he uses this to identify the correct PivotItem. But having identified it, you still can’t hide it if it’s a date and your PivotField date format is NOT a date.

    Using Jon’s approach, is there any reason one can’t simply use the PivotItem object assigned to Pi to hide the found PivotItem?

    Both of these snippets appear to work with mixed data types in the field….

     For Each Pi In PT.PivotFields("Order Month/Year").PivotItems
     If Format(Pi, "yyyy/mm/dd") = TheDate _
        And Pi.RecordCount Then _
           Pi.Visible = False
     Next Pi

    Or…

     With PT.PivotFields("Order Month/Year")
        For i = 1 To .PivotItems.Count
           With .PivotItems(i)
              If Format(.Caption, "yyyy/mm/dd") = TheDate And _
                 .Visible And .RecordCount Then _
                 .Visible = False
           End With
        Next i
     End With
    
  5. Hi Jeremy. Sorry, your comment has escaped my notice until just now.
    The issue remains that unless you have Excel 2013, you still can’t hide it if it’s a date and your PivotField date format is NOT a date.

    Run this code on a new sheet, and you’ll see what I mean:

    Sub WhatThe_v2()
    
    Dim pf As PivotField
    Dim pc As PivotCache
    Dim pt As PivotTable
    Dim pi As PivotItem
    Dim rng As Range
    
    [A1].Value = "Pivot"
    [A2].Value = "=TODAY()"
    [A3].Value = "=VALUE(TODAY())"
    
    Set rng = Cells(ActiveSheet.UsedRange.Row, ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column + 1)
    Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=[A1].CurrentRegion)
    Set pt = pc.CreatePivotTable(TableDestination:=rng)
    Set pf = ActiveSheet.PivotTables(1).PivotFields(1)
    
    pf.NumberFormat = "d/mm/yyyy"
            
    For Each pi In pf.PivotItems
    pi.Visible = True
    Next pi
    
    pf.NumberFormat = "General"
            
    For Each pi In pf.PivotItems
    pi.Visible = True
    Next pi
    
    
    End Sub
    

    IF you’re running Excel 2010 (and presumably earlier versions too) you’ll get this:
    Run-time error ‘1004’:
    Unable to set the Visible property of the PivotItem class

    And if you hit Debug, you’ll see that while the code could quite happily make those items visible while there was a date format applied to the field, it errored out when a General format was applied to the field.
    Image

    …and if you have mixed data types in that field (e.g. maybe some text) then you can’t simply set the field’s format to Date, because Excel won’t let you change formats for fields with mixed data types.

    Interestingly, this seems to have been fixed in Excel 2013 i.e. the above code runs without error.

  6. Hi again, Jeremy. I see you tested this in the field and had no problems. Can you advise what version of Excel are you using, and what happens if you run the code snippet I posted in the above comment?

    Cheers

  7. Hi Jeff, I currently use xl2013. When I posted in Nov 2013, I only had xl2010 installed and I was able to hide date values in a PivotField that was not formatted as a date using the code snippet I posted.

    I ran the WhatThe_v2 code just now on both xl2013 and xl2010 and didn’t receive an error on either.

    I re-ran with a statement a statement to add the RowField, which is what you probably intended. Still no errors.

    Set pt = pc.CreatePivotTable(TableDestination:=rng)

    pt.PivotFields(1).Orientation = xlRowField

    BTW Most people call me Jerry, but you're welcome to call me Jeremy since you were kind enough to fix my first-time Blog-poster error of using vBulletin style forum [Code] tags. :)

  8. Thanks Jerry. Sorry ’bout getting the name wrong. That’s really weird…the code bombs out for me big-time. The xlRowFIeld thing doesn’t matter, by the way.

    I’ll have to get some other folk to run the code on different machines, and see what the result is.

  9. It turns out this is a regional issue. According to IronyAaron in the comments at http://social.msdn.microsoft.com/Forums/office/en-US/01cb61c7-5e68-4a45-aeff-a70c6dbfe00f/excel-2007-accessing-pivotitemvisible-gives-error-if-the-field-item-value-is-a-date :
    When VBA recognizes the dates in the pivot cache, it reads the US version after parsing although the item is read as a locally formated string. This therefore causes VBA to fail when recognizing Date variables.
    Only real solution is to get the user to change regional settings in Windows to US. Good luck with that.

  10. Hi Jeff!
    I also had a problem with dates in PivotTable. I’ve found your blog and try every solution. Nothing worked.
    Because I’m working on Office2010 and there is no possibility to change the MS Office soon in our company, I had to figure something that will work. And I’ve thought about slicers. They are in Offce 2010 and they are nothing but filters. THAT WORKS. A little different than regular filters but hey, at least it’s something.
    “Data” in polish means singular date.

    Dim str As String
    ActiveWorkbook.SlicerCaches.Add(ActiveSheet.PivotTables("Pivot_Table1"), _
    "Data").Slicers.Add ActiveSheet, , "Data", "Data", 186, 760, 144, 188

    For Each Pi In ActiveSheet.PivotTables("Pivot_Table1").PivotFields("Data").PivotItems
    str = Replace(Pi.Caption, "/", "-") 'dates are with "/" but slices are with "-"
    ActiveWorkbook.SlicerCaches("Fragmentator_Data").SlicerItems(str).Selected = False
    Next Pi

    'ActiveWorkbook.SlicerCaches("Fragmentator_Data").Slicers("Data").Delete 'option

  11. I know this is an old thread but I just want to add my two cents worth which may help others with the same problems. Here is my approach to solving the above-mentioned issues with filtering dates on computers with different regional settings/date formats.

    1- I create a helper column which converts the date fields to its numeric value using the value() function.
    2- I use this helper column in the pivot table’s filter instead of using the original date field.
    3- Whenever the end-user inputs his start/end dates, I convert those to numeric values as well and store in a separate hidden cell.
    4- I then use all those numeric values in my vba scripts to filter the pivot tables, completely eliminating issues with date format, as you’re just working with numbers.

    Thanks, hopefully this helps others.

Leave a Reply

Your email address will not be published. Required fields are marked *