Date filter option not available in Pivot Table

[Macey141]

Hi

I am using Excel 2010 and have a spreadsheet containing just over 2000 rows, including a date field for each record. The date info is definitely in the correct date format because I can 'Filter' by year/ month etc when I put a filter on the header row. However, when I put the data into a pivot table, the 'Date Filter' option is greyed out.

Anyone any ideas how a activate the date filter option in the pivot table?

 

[sunnyland]

Hello there,

Have you check that you don't have a value that is not a date?

In an excel column, filter by date will still appear even if one data is not a date specially if the not date is not the first row under the heading.
Ie: type date as heading
type 3/05/09
type 1/06/10
type 39/03/05
Then filter the data.

The date filter will still display even if the last entry is not a real date.

However, in a pivot filter by date will not appear if one data is not in a date format. You may have instead a label filter with the value filter.

May be worth checking that you don't have something that is not a real date. Or is apparently a date but it is not ie : 31/04/2011

You can use the conditional format =istext(cellref) and color ie in red then filter by color red to check which one are not real date.

Hope this is helpful.