Pivotfilter on date- problem formatting field or named range used as criteria

[beckyinDC]

I am guessing this may be a date formatting 101 question...

I have a pivot table based upon data that was brought into the spreadsheet from another system. There is a date field (Date Opened)- values come in with a timestamp.

on pivot table, I manually group that date field and can filter using the dropdown and selecting a date. I want that to happen automatically so..:

ActiveSheet.PivotTables("TodaysTPRS").PivotFields("Date Opened").PivotFilters.Add _
Type:=xlSpecificDate, Value1:=[officialDSRReportDate]

officialDSRReportDate is a named range that gets populated programmaticlly when the daily report is run.

but that wasnt working so I tried using:

ActiveSheet.PivotTables("TodaysTPRS").PivotFields("Date Opened").PivotFilters.Add _
Type:=xlSpecificDate, Value1:="4/26/2011"

Either way (with hard-coded date or officialDSRReportDate - both set to a date that I see as a Date Opened value on the pivottable), I dont get a list filtered to that value.

Instead all columns for that pivotfield dissapear. In the field list I can click on the filtered column...I then see checkmarks next to all values (that seems odd to me since nothing is displayed). No error returned.

Selecting clear filter gets the columns back.

Here is the code that creates officialDSRReportDate. OfficialDate is a variable of type Long, and DSRReportDate is a named range that is calculated off of 2 named ranges that are cells in the spreadsheet- one with a value of "=Today" and the otther allows users to override that date.

officialDate = [DSRReportDate]
Application.Names("OfficialDSRReportDate").RefersTo = "= " & officialDate ' [DSRReportDate] doesnt work- looks good in names manager, but not on exec summary

in names manager I see a date value stored for OfficialDSRReportDate...40659. I may try reformatting that but right now am working on idea that I want to see VBA set up the filter against that field...I can do it manually, but must be doing something wrong programatically since cant even hard code VBA to make it work...

tried creating a formatted date column based off of original Date Open column- sample formula in spreadsheet :

=DATEVALUE(TEXT(I30,"MM/DD/YYYY"))

I am using excel 2007

in pivottable field settings I set that new pivotfield to number format of short date because it was a 5-digit date value. I get error 1004 when I try to filter using criteria "4/26/2011" against that pivotfield.

I was trying to do due-diligence but would appreciate help- thanks in advance-

Becky

 

[beckyinDC]

some progress-

created new pivot table on another worksheet in same workbook

CAN filter with VBA on the date if hard-coded.
CAN filter with VBA using a variable filled with the officialDSRReportDate variable. what works:

Sub datesagaaaaain()
Dim ofdate As Date
ofdate = [officialDSRReportDate]
ActiveSheet.PivotTables("testtable").PivotFields("somedate").PivotFilters.Add Type:=xlSpecificDate, Value1:=ofdate
End Sub

took that code back to other pivottable...and it didnt work- here is non-working code

Sub datesfilter()
Dim ofdate As Date
ofdate = [officialDSRReportDate]
ActiveSheet.PivotTables("TodaysTPRS").PivotFields("FORMATTED DATE OPEN").PivotFilters.Add Type:=xlSpecificDate, Value1:=ofdate
End Sub

so I think it has something to do with formatting of date field in pivot table. Will review tomorrow- any help appreciated as usual- thanks!

 

[beckyinDC]

revisiting my problem- :

Pivot table has 2 columns - I could work off either one-
date with timestamp
calc column based upon first column- seems to be just date value
formula:

=DATEVALUE(TEXT(I31,"MM/DD/YYYY"))

both are pulled into pivot table. I have been switching them out for the row fields (so only 1 is there at a time) when I try various VBA to filter the table.

I can filter using a timestamp value present in the dataset against that first col.

Try same thing against the calculated col -doesnt appear to be understood as a date. I tried filter type xlValueEquals, but that didnt work- error was invalid procedure call or argument.

back to idea that I can filter against the col with timestamp. I tried grouping, but filtering the list via VBA with the group filter option didnt seem to work- all data was removed, although several should have stayed.

I am going to try to use xldatebetween filter type and construct the arguments as being the full timestamp for the start and end of my variable date.- early morning and late night.

if anyone knows a way to filter using VBA against the grouped date ranges, that would be appreciated- doenst seem to work for me.

 

[beckyinDC]

I got this code to work:

Sub datesagain()
Dim odate As Date
odate = [officialDSRReportDate]
With ActiveSheet.PivotTables("TodaysTPRS").PivotFields("Date Opened")
    .ClearAllFilters
    .PivotFilters.Add Type:=xlDateBetween, Value1:=odate & " 12:00:00 AM", Value2:=odate & " 11:59:59 PM"
End With
End Sub

I guess that resolves this one though I wouldnt mind confirmation of my suspicion that the group by function and the pivotfilters dont work well together---? have a good day!