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..:
officialDSRReportDate is a named range that gets populated programmaticlly when the daily report is run.
but that wasnt working so I tried using:
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.
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
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..:
Code:
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:
Code:
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.
Code:
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