Custom date filter for a pivot table in XL2007

[IM2HARD2RESIST]

Hello,
I have a pivot table in which I have some data, and one of the fields is date. I have put this date in the 'report filter' , but here it shows me all the different distinct dates in the filter drop down. What I want is to put in a date range, say everything after 1st of June 2009, and then the pivot should consider only that data, but I couldn't find this option anywhere. Can it be done pls...

PS: I use XL 2007..

[DonkeyOte]

If as you say you're running XL2007 you can click on the Date Field Label Header -> Date Filters -> After -> enter your date.

Note: you can't remove the dates from the PT layout though if that's your requirement ?
(remember you can though Group them by Month & Year etc if that's your preference)

If you wish to remove the Date field from your Row section and / or you want another route... then another simple solution would be something along the lines of the below

a) On the Pivot Sheet enter the MIN Date of interest.

b) On your Data Sheet in a column adjacent to your existing data add a header, eg:

INCLUDE

Then insert a formula in all rows below that

=IF(SUM(datecell),datecell>'PivotSheet'!DateCell,"")
(adjust text in Italics to be appropriate cell references - not stipulated in your post)

Copy the above formula down for all rows (and if needed copy below current data for future use should your data expand etc)

-- Revise your PT range to include this new field INCLUDE

-- Set INCLUDE as a Report Filter and set to TRUE

The PT at this point will exclude earlier dates from analysis and you can remove the Date field from your Row Labels if preferred.

Should you change the date criteria you can then refresh the PT to update the output accordingly (assuming you're running on Auto Calc)

There are other methods using VBA which would perhaps be deemed more elegant but the above is a basic approach that should be easy enough to implement.