VBA Pivot Table Date Filter 2010 UK Format

[Jabba1963]

Does anyone have a definitive code that works for UK Regional Date setting for filtering a pivot table between a date range where the dates are based on two cell values outside the pivot table (of course) ?


I had the code working fine until I spotted that it wasn't working all the time and finally discovered that it was due to a US Date format issue and whilst I found lots of discussion on the subject in various places... regarding regional settings... no solution seemed apparent ?


The table is updated daily so initially my code was based on simply setting the new day pivot item to visible and the new day minus 7 to invisible - thus filtering for the past week (which is my aim).


The 16/05/2013 was set to invisible but debugging the code - the 09/05/2013 to invisible never executed - see below.

[pre]

Private Sub ApplyDateWkFilter()

Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim filterDate1 As Date, filterDate2 As Date

Application.ScreenUpdating = False

filterDate1 = CDate(Range("EvtDte"))
filterDate2 = CDate(Range("EvtDte_7"))

On Error Resume Next

Set pvtTable = Worksheets("DailyWatchCalc").PivotTables("PvtTblWkSentOutMWH")
Set pvtField = pvtTable.PivotFields("Event Date")

pvtTable.RefreshTable

For Each pvtItem In pvtField.PivotItems

Select Case CDate(pvtItem.Value)

Case filterDate1
pvtItem.Visible = True

Case filterDate2
pvtItem.Visible = False

Case Else

End Select

Next pvtItem

[/pre]

Any assistance gratefully received...


Regards

Jabba

 

[SirJB7]

Hi, Jabba1963!

I neither use US regional settings as I regularly use non English Excel versions (mostly Spanish and exceptionally Brazilian Portuguese) but all with dates in format dd/mm/yyyy and I didn't experienced those issues.

Maybe you'd consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!

 

[Jabba1963]

Thanks SirJB7 for getting back to me so quickly...


My spreadsheet is huge in volume 650,000 rows and many MB... so I went about creating a cut down version for testing and upload purposes as you suggested...


Typically however I can no longer recreate the problem and all appears to be working fine but this doesn't solve the problem back in my main spreadsheet so I will take an even closer look...


If I find the problem then I will ensure I update this thread accordingly.


Thanks again for your time.

 

[SirJB7]

Hi, Jabba1963!

Then you don't have any issue with PTs and filters, hence you should be having data issues. Check your data and if still unsolved try creating a sample file that includes the source entries with problems to be properly filtered and upload it.

Regards!

 

[Jabba1963]

RESOLVED - Found the answer... yeahhhhhh....


It boils down to Regional settings as originally suspected...


In the Pivot Table... the date field... (mine was under Row Labels)... click the drop down arrow... click field settings... at the bottom of the form control that opens - click on "Number Format" button control... (if this is not showing see appendix below) Select the Date format on left hand side... AND Select Type "14/03/2001" (3rd one down) on the right hand side NOT "*14/03/2001" (default 1st option)... the code then works as expected.


If the default option (top option) "*14/03/2001" is select it behaves strangely due to Regional settings and Microsoft defaulting internally to US date format so the code ends up comparing US Dates to UK Dates (in my case) and hence produces erratic results.


Regards

Jabba


Appendix - if the Number Format button control is not showing then it is because the pivot table data source range extends beyond the data... ie. row 1048576... to resolve, re-size the date source range to the end row of the data... change the field format as above and then change the data source back and the format will be maintained going forward. This was another irk I discovered with Pivot Tables sometime back.

 

[SirJB7]

Hi, Jabba1963!

Glad you solved it. Thanks for your feedback and sharing the explanation. Welcome back whenever needed or wanted.

Regards!