How To Use Filters in SharePoint to show items in the current Calendar Month

Date:November 26th, 2009 Author: Tags: , , ,
Category: Calculated Columns, Filter, SharePoint Ideas Comments:209 ;

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

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)
create-column To put this into SharePoint open your list, click Settings > List settings > Create Column and setup the following two columns.

  • The first column will be called something like Start of Month
  • Set the column type to Calculated and the data type to Date only
  • Uncheck the ‘Add to default view’ button as we want these columns to stay behind the scenes
  • The Start of Month column formula should be
    =DATE(YEAR([Due Date]), MONTH([Due Date]), 1)
  • The End of Month formula :-
    =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.

View-filter 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: , , ,

209 Responses to “How To Use Filters in SharePoint to show items in the current Calendar Month”

  1. Ryan says:

    @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.

  2. Ryan says:

    @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.

  3. Ryan says:

    @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.

  4. Voldo says:

    Doh ok, so it only works when using it on a view? Are there any 3rd party addons which allows this to work then?

  5. seema says:

    getting error for showing the current month
    error –> Filter value is not in supported date format
    Why is it so?

  6. Ryan says:

    @Seema – you must have made a mistake in the formula.

  7. Tom says:

    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?

  8. Ryan says:

    @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”

  9. Tom says:

    ahh, now it works. did somesthing wrong when i first tried it. Sorry. Thx anyway.

  10. shigady22 says:

    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

  11. Ryan says:

    @Shigady – Where are you trying to put this formula, in a calculated column, view filter or default value?

  12. shigady22 says:

    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

  13. shigady22 says:

    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

  14. shigady22 says:

    Ryan,

    Thanks for your responses, I got it to work.

  15. shigady22 says:

    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?

  16. Ryan says:

    @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.

  17. shigady22 says:

    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.

  18. Ryan says:

    @Dan – haha, no worries, all these brackets can make your eyes go wonky (o) (O) !

  19. Kerry says:

    Oh my god… you’re a legend! Works perfectly. 😀

  20. Jason says:

    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!

  21. Rob says:

    Is there a formula to get the last 24 hours?

    Thanks

    Rob

  22. Ryan says:

    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.

  23. paul says:

    I get a syntax error when I tried this using SP 2010; any ideas why?

    Thx

  24. paul says:

    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!

  25. Ryan says:

    @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.

  26. Kris says:

    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

  27. Ryan says:

    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?

  28. Kris says:

    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

  29. Ryan says:

    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).

  30. Kris says:

    Thanks Ryan

  31. Sharath says:

    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

  32. Christina says:

    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?

  33. Ryan says:

    @Christina – yes, you need to rename “Due Date” with your column name.

  34. Christina says:

    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

  35. Joe says:

    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

  36. Ryan says:

    @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!

  37. Joe says:

    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.

  38. Ryan says:

    @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.

  39. Janet says:

    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! 🙂

  40. Jorge says:

    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.

  41. Ryan says:

    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.

  42. Matt says:

    Excellent information. Exactly what I needed! Thanks!

  43. Lauren says:

    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?

  44. Ryan says:

    @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?

  45. Sivanesan.B says:

    Filter by current quarter in sharepoint list., http://sivanesanb.blogspot.com/

  46. Joshua W. says:

    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.

  47. Dan says:

    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

  48. Ryan says:

    @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

  49. Ryan says:

    @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

  50. Dan says:

    @ Ryan, thanks, you are the man!!!

Leave a Reply

Anti-Spam Quiz:

We need to confirm your email address so we have just sent you an email.


Please click the link in the email and you will have immediate access to the download