How to filter items in a list that have a specific date that falls in last, this or next month?

Question

I want to show only items with a specific date that falls in last, this or next month?

For example: If today is the 7/13/2010 it should show only the items between the 1st of June and the 31st of august. I could I achieve that?

Answer 1

You've got to turn the problem on its head and setup a calculated column for the start of the period in which an item should be displayed and the end of that period.

You then use a view filter to show only items where the [Today] is between those two dates.

(Yes you can use [Today] in filters, just not in calculated columns)

This example shows you how to to filter SharePoint list items in the current calendar month - it's a bit hard to grasp at first but look at the diagram and explanation and it will make sense.

To extend it to Last, This and Next month (assuming your date field is [Due Date]) you get the following calculated columns

StartPeriod = DATE(YEAR([Due Date]), MONTH([Due Date]-1, 1) 
EndPeriod = DATE(YEAR([Due Date]), MONTH([Due Date])+2,1)-1

and the filter

Where StartPeriod <= [Today]
AND EndPeriod >= [Today]