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
[…] This post was mentioned on Twitter by Veronique Palmer, Sandip. Sandip said: Nice article on filtering #SharePoint calendars OOB from Pentalogic http://bit.ly/66tD3U […]
The calculated columns mentioned above are only good for one day. Tomorrow, if nothing has changed in the underlying list item (i.e., an Update has not occurred) the calculated column is inaccurate because it is not automatically recalculated. This is a major weakness of SharePoint – the calculated columns are “static” (until somebody makes a change to the underlying list item) instead of “dynamic” (recalculated each time they are ACCESSED, regardless of whether the underlying list item has been updated). An admittedly awkward solution is to define a workflow that runs every day just after midnight that issues a System.Update() call on ever single list item in any list that has these types of calculated columns to force those columns to recalculate with the new value of [Today]. The real need is for two types of Calculated columns: Static (calculated once until list item is updated) and Dynamic (real-time calculation based on currrent Date/Time substituted for [Today]).
Hi Fred,
I think you have misread the article a little. You are absolutely correct about the [Today] trick in calculated columns not updating and its something I’ve blogged about before http://blog.pentalogic.net/2008/11/truth-about-using-today-in-calculated-columns/
However – the post above does not use this trick, and the calculations such as “=DATE(YEAR([Due Date]), MONTH([Due Date]), 1)” do not change with the current date, only when the list items [Due Date] changes.
Also you can use Today in filters – this is evaluated each time the fitter is ran.
Follow the walkthrough and I’ll guarantee it works, $100 to the charity of your choice if I am being a muppet!
The linked post would work even better if it used a content type with “hidden” calculated columns to calculate FirstShowDate and LastShowDate values and then filter for FirstShowDate[Today]
Hi John – its a good idea for some circumstances (and I’ve taken the liberty of copying your comment to the main article) but not something that I wanted to put in the post because :-
a) The main ‘take away’ point of the article is how to turn the problem on its head so you can work around the limitations of calculated columns and filters – the use of content types/site columns might hide this point under extra complexity
b) You can’t do this (AFAIK) using the normal web interface so its not suitable for ‘end users’
Having said that, for someone setting up a list template or similar and wanting to give it views like “Due this week” “Due this month” etc then your idea is perfect.
Thank for the comment.
great stuff!
thanks for this info. it really helped me a lot. however, i would like to ask what if i would like to filter only those item that was created last month? say today is 12/03/2009, and i would like to show only those item created between 11/01/2009 and 11/30/2009. I tried the one specified under previous months, but the filter returned me nothing. please i really need your advice. thanks in advance!
@JMB I would check the following :-
1) Did you change [Due Date] to [Created] in the formula?
2) Check a few list items – are the start/end of month dates being worked out correctly? If not the problem is in your formula, if so then the problem is in the Filter.
If you’re still struggling with this then send me a few screnshots showing your list, the formula and the filter portion of your view and I’ll do what I can.
http://www.pentalogic.net/about-us/contact-us.aspx
Hi Ryan,
Thanks for the reply Ryan. First, I have followed each steps you specified correctly. The formula is perfect.
But I think, I have used the wrong term “created”. Let me state my requirements:
1. I have a custom list where I will put all items that was reported a month ago.
2. I have included the custom field [Date Reported] whose value is equal to the date when the items were actually being reported. This is not necessarily the same date as it was added on the list.
Ex. Item A was reported last 11/15/2009, but I have included it on the Sharepoint list today 12/06/2009, the [Date Reported] column should have a value of 11/15/2009 rather than 12/06/2009.
3. I need to created a filter where I can view all the items whose [Date Reported] value were within a month ago. In case of the example above, I want to view all of the items for the month of November only. In this case, Item A should be included on the return value of the filter.
I hope you could help me on this. The formula to get the value of start and end of previous month is ok. But I’m just at a lost on how to do the filter when the comparison to use is a custom field rather than the keyword [Today].
@JMB – You might need to read through the example again as I think you misunderstood the key part. Also – are you wanting to show things reported in the last 30 rolling days within the previous calendar month?
The calculated columns are responsible for working out the start and the end of the month in which you want to display the item, and the filter applies, well the filter.
So in the column formulas you would use [Date Reported] instead of [Due Date] however in the filters you always use [Today] which is a tag for Todays date.
This might be easier to sort out on email so get in touch on the contact us link above.
Nice blog Ryan. Thanks for posting this, it was very helpful.
Hi Ryan,
I have sent you and email regarding the details of my problem on previous month filtering. Your help is badly needed. Thanks in advance!!!
Awesome! Just what I was looking for!
Hi Ryan!!!
Thank you for the awesome instruction you have shared! I already got the results I am expecting based on your reply to your email! You’re the best!!!
Hi Ryan, When I setup the Start of Month column I get the following generic error message:
Error
The formula contains reference(s) to field(s).
Troubleshoot issues with Windows SharePoint Services.
Any idea’s????? Thanks!
@Jeanna – Haha, SharePoints famously helpful error messages!
I think you’re getting that error because you are trying to put the formula in the Default Value of a column, rather than create a calculated column.
A default value is… erm… the default value given to a field when a new record is created but can be overwritten. In effect what you’re asking it to do is work out a value based on another value that hasn’t been entered at the time the “Add New” button is clicked and the formula calculation ran.
Have a look at the middle picture above and check you’ve got the right options selected (Calculated, Date and Time etc) and the formula in the right area. If you’re still having trouble can you send a screenshot to me? http://www.pentalogic.net/about/contact-us
Yes, the error messages are so not helpful! Thanks so much, I was putting it in the wrong place – DOH!
Hi Ryan, Great post! I ran into an interesting related problem. I used this post to impress my boss, then she asked if I could create a view that shows items due in each fiscal quarter. My first idea was to create 8 calculate columns, a start and end for each quarter: “Start of Q1”, “End of Q1”, “Start of Q2”, “End of Q2”, etc. Then create a view for each quarter: “Due in Q1”, “Due in Q2”, etc.
The problem is that our fiscal Q1 goes from November 1 to January 31, so it spans one year to the next. For all other quarters I think it’s simple. For example, Q2 (Feb 1 to April 30) would be:
Start of Q2: =DATE(YEAR([Due Date]),2,1)
End of Q2: =DATE(YEAR([Due Date]),4,30)
But for Q1, I’m not sure what do do with the YEAR in the calculation because the year you are viewing it might be different than the year it’s due, but it’s still in the same quarter. For example, if I was looking at the view in Nov/Dec 2009 I would see tasks due in Jan 2009 rather than Jan 2010; and if I was looking at the view in Jan 2010 I would see tasks due in Nov/Dec 2010 rather than Nov/Dec 2009. Any idea how to modify the calculation for Q1?
Going further than this, how would you approach this if you just wanted one general view called “Due this Quarter”? How would you filter the view? Would you need additional calculated columns?
Ryan,
I would like to know if there is a way to display only months at the top of the Gantt chart, I am new to this part of SharePoint I am using the jquery slider code and have tried with and without calculated columns, any help you or the other users would be great.
Thank you for your time…
Robert
@Josh – Interesting question, I’ll have to give it some thought.
@Robert – Christophe is the expert on using Javascript to modify the behaviour of the OOTB Gantt chart.
Alternatively there are some 3rd party web parts (blatant plug time!) that give you much more flexibility over the built in Gantt Chart such as Planner for SharePoint.
Ryan,
I have been away for some time but coming back to the office and seeing your response really made me happy! 😉
I had no problems following your instructions to set things up, and I am no pro at this whole sharepoint stuff so you must take this as a real compliment.
Regards,
Ed.
Hey Ryan! This has been a big help and after reading this and some of your other posts I am still left with one question.
My list does not have a “due date” or anything. All I want to do is in any given month, see only documents in the list that were created that month. I understand the calculated fields dont update themselves. But what is a good way to make this happen? If I have overlooked the solution on any of your pages I aplogize, but please point it out to me.
Thanks in advance!
@Domenic – every list in SharePoint has a Created and Modified field that contains the date/time when the record was created and last modified. So for your use instead of using “Due Date” you can use “Created” to get a list of all items created in the calendar month.
Ryan,
That is what I did from the start, however, now I face the issue of everyday when I create a new document it wont show up. I pretty much have to go to the settings page and go into the settings for both “First Day” and “Last Day” columns and click OK. Basicly to “refresh” the colums. This obviously is not practical but I do not know how to fix this.
Any ideas?
And again, I apologize if this is already covered somewhere and I have overlooked it.
@Domenic – It works fine when I’ve done a quick test here. Get in touch (Contact Us on main website) with some screenshots and I’ll see if I can figure out whats going wrong.
Ryan,
Real quick, forgot to mention this fact. I am not using this on a list, I am using it in a form library. Trying to show submitted forms for “current month” only.
@Domenic – that doesn’t matter as its still a list underneath.
From your comments re: “modifying the settings” its sounds like you may be mixing in the calculated column ‘Today’ trick (which doesn’t work) up with this method so please re-read the post step by step to see if its anything obvious. If not then send over some screenshots of your list data/calculated field formula/view filters etc and I’ll see what I can do.
sent screen shots to support email with your name in subject line.
Ryan,
Great stuff and easy to follow. How about if I only have mm/dd in my field? I’m trying to be sensitive and not ask for the yyyy portion for birthdays. And like this example I only want to show those birthdays in the current month.
Thanks!
Hi there
Does anyone know any easier way for me to be able to group an Actions Log by Modified Date (but have it grouped as below rather than by each date?):
-Today
-Yesterday
-The Week
-Last Week
-This Month
-Last Month
-Older
Would appreciate the help!
Stephen
Thanks for these tips. I used it today to create a calndar month view of contributions to a discussion board. Very easy to follow for a non-techie.
James.
Hi Ryan, thank you very much for this post, saved my day!
Hey, are your formulas for previous month and next month backwards? It’s been awhile since I’ve done any coding, but from looking at them, they look like one is caluclating the other. I’ll try to test them out on my site as I need them, but from first look…
Also, I want to do the previous month and next month feature, but I don’t see how [Today] can be used in the view. I can try to add or subtract 30 but since the months always change, there will be a chance that it doesn’t come out right (like on the 31st of a month). How do I make sure that my view shows only those tasks labled with last months date? In the current month, I get the more than and less than feature. However, next month would both require more than [Today] but that also includes the month after that. The same is true for previous month. Thanks for your insight.
Doh…I think my last post just explained why your formula is correct as mentioned. Since we are comparing the answer to [Today], they previous month has to add a month and the next month has to subtract a month to make it work. Sorry for my above posts..
@Robert – no problem, having to attack this problem backwards makes it a bit of a mind bender at first!
Hi
I want to show items which are after 2 hours to present time in a list view. Please let me know as this includes working against TIME too where the TODAY cannot be used for TIME calculations.
Or let me know how to get the current time through any calculated column.
Regards,
Kiran
Hi,
Firstly, thanks for your blog, I think it’s great. I’m struggling with one part though – maybe I can describe and I’m sure you can point out where i’m going wrong.
I want to display all items in the list which will be overdue by the end of the month. So, that includes items which are overdue from previous months. I’ve created an “End of Month” column for each entry.
I created a filter for where “End of Month” is greater than today. But this brings back all future months. So, I changed the filter to be “End Of Month” is greater than [Today]+31. But I think there will be a few days in the year (mostly around month end) where our users will get the wrong data. As most of our users tend to leave everything until month end to sort out, it’s probably not okay to leave it like that.
What do you suggest? I want to show all the items which will be overdue by the end of this month, but also include those from previous months too. Its probably really simple.
Thanks again, think your blog is ace.
I’ve been using this trick for some time (thank you very much for this) and I noticed something odd today. For no apparent reason, when I went into my filtered list today (the 31st of August) it was only showing my own cases. I thought this seemed rather odd…like the permissions had been change or somehow a filter showing only my stuff had slipped in.
After further investigation, I decided to show the FirstDay and LastDay columns. For some inexplicable reasons, the dates were messed up for everyone but me. A couple other people were showing 7/31/2010 as the first day and 8/30/2010 as the last day of the month. Furthermore, when I looked further down the list, in the middle of July, one person’s dates went from being correct (7/1-7/31) to wrong (6/30-7/30). I tried what Domenic here had done and went into the First and LastDay columns and just hit okay, lo and behold, everything fixed itself right up.
Is this some weird bug, or is there something I should be watching for? I’ve been using this for 2 months or so now so I’m not entirely sure if it’s always done that or not.
Exactly what I needed. Now I no longer need to manually redefine my \Current Month\ view every month. Thank you SO MUCH!
You just saved Christmas, Ryan…I fought with this issue several months ago and just now got back around to dealing with it again. Problem solved!!!
Whatever happened to Fred Morrison, anyway?
Anyway, thanks a lot!
@RJ – I am no santa, well maybe a Bad Santa but thanks!
Fred – never heard back from him so I’ve still got my $100 😉
This works perfect! thanks for taking the time to write this 🙂
Hello,
I’m fairly new to SP 2010 so please forgive me for this post. I found your post regarding items shown in Current Month so useful and have used it on one of my sites but now I need to create another list which shows items created in current week. I tried to use this:
StartWeek =[Proposal Date] – WEEKDAY([Proposal Date]) +1
EndWeek =[Proposal Date] +7 – WEEKDAY([Proposal Date])
and in the list filter I have:
StartWeek is less than or equal to [Today]
EndWeek is greater than or equal to [Today]
I feel I have something wrong in the filter as I am only seeing items with todays date in the Proposal Date field and if I create something in advance for 18/Feb/11 it doesn’t display. Any ideas?
Many thanks in advance.
Hayley
here is my Previous Month which allows for a yes no response for filtering currently based on the Created date. Inspired by this page.
=IF(AND([Created]=DATE(YEAR([Today]),MONTH([Today])-1,1)),”Yes”,”No”)
Opps, Pasted wrong one.
=IF(AND(Created=DATE(YEAR(Today),MONTH(Today)-1,1)),”Yes”,”No”)
Hi All,
I tried adding 2 columns Start_Month and End_Month by replacing the Due_Date field with a field know as Incident Date (the field is a DateTime) but when I used the filter condition of [Today] in between the start month and end month I got no rows selected. The formulas I used are similar . Star Month Formula is =DATE(YEAR([Incident Date]),MONTH([Incident Date]),1) and End_Month is =DATE(YEAR([Incident Date]),MONTH([Incident Date])+1,1)-1 . Please help out . Thanks a lot in advance.
My forumla isn’t pasting correctly , sorry for any confusion. Seems that the page removes some of the code when I submit.
For a copy of the Previous Month formula email me voldoxATgmail.com
Hi Voldo ,
Can you help me too. I need to pull a monthly report from Sharepoint . Our report has a field with datetime format something like 1/1/2011 12:16 AM and I used the formula given in the blog . Pls see my previous post. Please advice where I am making a mistake in the formula.
Thanks for your help.