Its very easy using the Filter feature of SharePoint views to show only items completed in the last 30 days or items due in the next X days; for example :-
Completed >= [Today]-30
On the Advanced SharePoint View and Filter techniques post Ed asked how you could filter for the current calendar month rather than a rolling 30 day period (for example all tasks due this calendar month).
Like all great questions it seemed simple enough – something like only showing items where the Month and Year of the due date = the Month and Year of the current date.
Alas, we can’t use [Today] in calculated columns and we can’t use functions like Month([Due Date]) in the filter – so this approach will not work.
The solution is to turn this on its head – its a bit weird, so bear with me!
We need to have two calculated fields, one showing the first day of the month that the due date is in and one showing the last day of the month that the due date is in.
We can then use the filter to show only records where
[Today] >= First Day Of Month
AND
[Today] <= Last Day Of Month
So looking at this in graphical form we have :-
Timeline
- In this diagram the current date is 15th Nov (the orange dotted line).
- Task 1 & 2 should be shown if the current date [Today] is in between the first and the last day of November (the blue line)
- Task 3 will not be shown until [Today] is between the first and the last day of December (the blue line)
 |
To put this into SharePoint open your list, click Settings > List settings > Create Column and setup the following two columns.
=DATE(YEAR([Due Date]), MONTH([Due Date]), 1)
=DATE(YEAR([Due Date]), MONTH([Due Date])+1,1)-1
(Which is the first day of next month - 1)
|
View a few records to check the formula is working OK and you see the correct dates.
 |
Now create a new view, call it something like “Due This Month” and setup the filter for
Start of Month is less than or equal to [Today] AND End of Month is greater than or equal to [Today] |
Further tips
You can use this with date fields other than [Due Date] – e.g. [Created] for items created in the current month, [Modified] for items well… urm… modified in the current month!
Using the same technique you can also use the following formula
The current week (Sunday to Saturday inclusive)
Start Week =[Due Date] - WEEKDAY([Due Date]) +1 End Week =[Due Date] +7 - WEEKDAY([Due Date]) (For other definitions of the a week see this post)
The previous month
Start Month =DATE(YEAR([Due Date]), MONTH([Due Date])+1, 1) End Month =DATE(YEAR([Due Date]), MONTH([Due Date])+2,1)-1
The next month
Start Month =DATE(YEAR([Due Date]), MONTH([Due Date])-1, 1) End Month =DATE(YEAR([Due Date]), MONTH([Due Date]),1)-1
The current year
Start Year =DATE(YEAR([Due Date]),1,1) End Year =DATE(YEAR([Due Date]),12,31)
Edit
I should make clear that this technique will not work correctly on Event lists with recurring event as SharePoint only records the start time of the first event, not each occurrence in the series.
References
| Free SharePoint Calculated Column Cheat Sheet |
| a handy 3 page reference for calculated column functions and formulas. |
 |
Tags: Calculated Columns, Filter, Tip, View
@Danielle – You just change the “End Month” columns formula to be : End Month =DATE(YEAR([Due Date]), MONTH([Due Date])+2,1)-1
Hi Ryan,
Just wanted to let you know that the “Date and Time Functions” link under References goes to an error page. Thanks for the informative article!
My boss wants a twist on this, but I’m not sure this method will even work for it.
We have an Advertising Calendar which shows when our confirmed marketing events will happen.
At the bottom of each month’s page, she would like a list web part that filters to show only upcoming, non-scheduled items for that month.
For example, when you are in January, the web part would show the info for all items where a “Month” column matches “January”. If you switch to February in the calendar, then the web part would filter to show only items where the “Month” column matches “February” and so on.
My initial thought was to try using JavaScript to grab the Title for the page and compare that against the “Month” column or something similar, but I would prefer an easier method.
Any idea on how to accomplish this?
Hi Sean – its a good question but not really related to this article, you would be better asking on a forum such as https://sharepoint.stackexchange.com/
Hi Ryan
Could this be set up to show the next 3 months?
Yes – there is a formula at the bottom showing how to do this for the next calendar month – should be easy to spot how it needs to change for ‘next 3 calendar months’
I want to set a filter which shows all the content where deadline is from today to end of the wee. how to set that up.
This is Awesome…thanks for the great tip!
Hi,
I have a requirement to create column with date for specific date of every the event will happen. How to setup a column that calculates the specific date of every month automatically.
Thank you.