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?


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]
  • Brilliant, thank you Ryan! I had to move one bracket (before the -1) to make it work correctly: StartPeriod = DATE(YEAR([Due Date]), MONTH([Due Date])-1, 1) – mikedevon Jul 14 '11 at 9:16
  • Opps! - cheers, editing now. – Ryan Jul 14 '11 at 12:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.