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
@Hayley – That looks right to me, so get in touch with us and send over some screenshots of your formula, view setup and values in your Proposal Date, StartWeek and EndWeek and I’ll see what I can do.
@Voldo – I am not sure that will work – are you putting that formula into a calculated column? If so then you should know that the fake ‘Today’ column trick doesn’t work.
@new_to_sharepoint – get in touch with us and send over some screenshots of your formula, view setup and values in your Incident Date, Start and End Month and I’ll see what I can do.
Doh ok, so it only works when using it on a view? Are there any 3rd party addons which allows this to work then?
getting error for showing the current month
error –> Filter value is not in supported date format
Why is it so?
@Seema – you must have made a mistake in the formula.
Thanks for this Ryan.
Now I want to create a view listing all the items of the next month (due date is between 1st day of next month and last day of next month). To set the 1st and last day is not the problem. But I cannot compare two colums in the filter settings (eg. “FirstDayOfMonth” less than or equal to “Due Date” AND “Due Date” is less than or equal to “LastDayOfMonth”. I was thinking of another column as a helper but coudnt find a solution… any idea?
@Tom – there is an example at the bottom of the post for “The next month” – the basic method is exactly the same as “This Month”
ahh, now it works. did somesthing wrong when i first tried it. Sorry. Thx anyway.
I get an error when trying to put this in my code. The error is: The formula contains reference(s) to field(s).
This is what I used,please help.
=[Sit Rep Due Date] – WEEKDAY([Sit Rep Due Date]) +1
@Shigady – Where are you trying to put this formula, in a calculated column, view filter or default value?
Ryan,
I can’t seem to get this to work. If I have an InfoPath 2007 form that is populating a custom content type and being submitted to SharePoint, does this change anything. We are using MOSS 2007. I use a date picker in my form, and this form submits to a SharePoint list. I then tried to create a calculated column to filter by the due date custom column. Unfrotunately, it returns all data and not just the data that fits the filter. Does using InfoPath date picker cause any problems within SharePoint?
Thanks,
Dan
Ryan,
I am trying to put it in a calculated column. My goal is to have forms that are submitted by users populate a SharePoint list. I then want to create a view that will only show the items for that current week based on the due date selected by the user when they submitted the form.
Thanks,
Dan
Ryan,
Thanks for your responses, I got it to work.
Ryan,
I was able to get Current Week to work, but Current Month does not return any data. I have a calculated Column labeled MonthStart and use this code: =DATE(YEAR([Sit Rep Due Date]),MONTH([Sit Rep Due Date]),1)
I then have another calculated column labeled MonthEnd and the code: =DATE(YEAR([Sit Rep Due Date]),+1,1)-1 However, SharePoint keeps dropping the + sign, so I am not sure if this has anything to do with it, so the code after I click ok is: =DATE(YEAR([Sit Rep Due Date]),1,1)-1
When I create my view and reference in the filter the two columns above, I do not get any data returned.
What am I doing wrong?
@Dan – You’ve got the formula wrong, you’ve got
=DATE(YEAR([Sit Rep Due Date]),1,1)-1
Should be
=DATE(YEAR([Sit Rep Due Date]), MONTH([Sit Rep Due Date])+1,1)-1
If you’re struggling to get these working then look at the values in the StartMonth/EndMonth columns wihtout a filter and you will be able to see where the propblems are – in your case instead of End of Month being the end of the month your formula would have shown the end of the *previous* month.
Thanks Ryan,
I knew it had to be something I did not do correctly. I could have sworn I verified it to be exact. Thanks for being my second set of eyes.
@Dan – haha, no worries, all these brackets can make your eyes go wonky (o) (O) !
Oh my god… you’re a legend! Works perfectly. 😀
Awesome post. Well put. I’ve done rolling periods dozens of times, but needed this and I couldn’t seem to wrap my head around it. Thanks!
Is there a formula to get the last 24 hours?
Thanks
Rob
Rob – not that I know off, you can’t do anything like [Now] in SharePoint, you’re limited to the date portion of a datetime field.
I get a syntax error when I tried this using SP 2010; any ideas why?
Thx
Sorry, I get this:
“Error
The formula contains reference(s) to field(s).”
I tried a simpler formula:
YEAR([Created])
and I still get the same error. Help!
@Paul – you’re putting the formula in the wrong place. You’re using a Date/Time field with the default value setting (which sets up a default value for a new record) – this can’t refer to field in the record because at the time you create a new record it doesn’t yet exist to pick values from it – hence the message.
Please ensure you create a Calculated field – set the type to Date Time and input the formula there.
Hi Ryan
I’m trying to implement this but I’m having a bit of trouble. I have a list that contains events. Some of these events can span over a few days. I have a first date and last date columns.
So im trying to have a DailyFaqs view that will show events that are between the first date and last date 06/30/11 – 07/04/11 by using [today]
hope that you can point me in the right direction
Kris – I think you just need to setup a view with a filter of
Start Date = [Today]
(in other words Today is between the start and the end date).
Does this work for you, if not can you clarify what you’re trying to do?
Hi Ryan
I’m not sure if that will work, so I will try and clarify my problem.
As I said, I have a list of events that are happening throughout the year.
I then want a view that shows “whats on” today.
The problem I am having is that when I have an event that spans more than one day.
I thought I could use your above solution to get what I want. Id like it to show the event when [today] occurs at anytime in between Date A and Date B
Opps – the formula got mangled when I put in angle brackets. You don’t need to follow the method on this page (thats for something else) you simply need a view with the following filter
Start Date is less than or equal to [Today]
End Date is greater than or equal to [Today]
(in other words show records where [Today] is between the start and the end date).
Thanks Ryan
hi ,
i want to show a calendar with yearly view.(like the day,week,month view in calendar).could you help me to get that one. and also i need a calendar with custom two dates.(from 15th august to 15 december 2011).how to get that.please help.many thanks
regards
sharath
When I enter the formula, I get this answer:
“The formula refers to a column that does not exist. Check the formula for spelling mistakes or change the non-existing column to an existing column.”
Does that mean that “Due Date” should be replaced with the name of an actual column in my list?
@Christina – yes, you need to rename “Due Date” with your column name.
Thank you, that worked! I also had the wrong code in the Month End filter, but fixed that. Now all I have to do is convince the user (enthusiastic but inexperienced) that it’s the coolest thing ever. : D
I am trying to use your method to create a view of what was completed last month. I have the calculated fields showing the month range of the completed date but for some reason cannot understand how to narrow the view down to what happened last month. Please let me know how this can be done.
Thanks
joe
@Joe – erm – the details are in the blog, I’ve no idea where you’ve gone wrong but I would suggest going through it again and double checking that a) The right dates are appearing in the start of month, end of month fields and b) you’ve got the filter in the view right.
Good luck!
Ryan
I do and have gone through it many times. The date columns are correct but the view shows everything including dates before the range but not after..another words date columns show 7/1/2011 – 8/1/2011….view will also include items from 6/x/2011 based on how you wrote this. It isnt showing what is between those dates only. I could do this filter with just the completed date.
@Joe – “have gone through it many times” – you’re missing something – sorry, no other way to say it! 😉 Take a load of screenshots of your list, the formula you’ve got in the columns, the filter your using etc and send them to us and I’ll see what I can do.
Ryan, you rock – thanks for this awesome info – so glad I googled and found this before I spent too much time trying to figure it out myself! 🙂
Ryan, thanks for the grerat post! You did a great job explaining the solution and documenting it in great detail.
I’d be curious to know if anyone has come up with a solution for filtering on recurring events.
Cheers Jorge! re:Recurring Events, not that I know off and knowing how recurring events are implemented I don’t think its going to be possible without some pretty serious code and custom web parts.
Excellent information. Exactly what I needed! Thanks!
I have used the correct filtering and calculated fields, but my previous month view shows one item from the current month. If I include the previous month start and end date fields in the view, it shows that the range should be from 10/1/2011 to 10/31/2011 and it pulls the issues from september which is great, but it includes one issue from 10/3/2011 and the start and end date range for that is not 10/1/2011 to 10/31/2011, it’s 11/1/2011 to 11/30/2011. Any clue why it would do this?
@Lauren – thats an interesting one! My first thought is are you sure that you’ve got an AND filter (Today > Start AND Today < End) or could an OR filter have sneaked in?
Filter by current quarter in sharepoint list., http://sivanesanb.blogspot.com/
I have been trying to figure out how to filter a view by past three months. I have wracked my brain, any help will be much appreciated.
Ryan,
Is it possible to use a calculated column and filter to show previous years (plural). I see how to do it for 1 previous year, but what if you wanted it to show the past 4 years?
Thanks,
Dan
@Joshua – the answer is above. You setup up a window to show the item for the next 3 calendar months. Its the same method as week/month, just with 3 rather than 1
Assuming you mean this month and last TWO calendar months then its
Start Month =DATE(YEAR([Due Date]), MONTH([Due Date]), 1)
End Month =DATE(YEAR([Due Date]), MONTH([Due Date])+3,1)-1
@Dan – are you talking about 4 calendar years (e.g. 2008,2009,2010 & 2011) or 4 years (365*4 days)
Assuming its the former then its exactly the same method as above but with a different start/end window.
Start =DATE(YEAR([Due Date]), 1, 1)
End =DATE(YEAR([Due Date])+4, 12,31)-1
@ Ryan, thanks, you are the man!!!