RSS Feed Is this your first time here? Get regular updates

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:150 ;

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

Did you enjoy this post? Get the next one by RSS or by email

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

  1. Ryan says:

    @Dan – haha, cheers!

  2. Richard says:

    I tried to use this to filter items modified in the current month, it all worked using
    =DATE(YEAR(Modified),MONTH(Modified),1)

    However during the weekend a Information Management Policy ran and now all actions are showing in the view, but the individual ‘last modified dates’ have not changed

    EG an item with as last modified on 01/09/2010 by Richard now shows month start as 01/01/2012

  3. Ryan says:

    @Richard – interesting problem! Just wondering – if you edit the start/end calc columns (list settings > column) and save it (you don’t have to make any changes, but it will still refresh all the data) does it show correctly or not?

  4. Richard says:

    @ Ryan

    The plot thickens – I modified the calculations in both the month start and end columns and now its working correctly…..

    I did notice that the [] had disappeared from around the Modified identifier

    The calculations were input with them last week,

    Last week – =DATE(YEAR([Modified]),MONTH([Modified]),1)
    This Morning – =DATE(YEAR(Modified),MONTH(Modified),1)
    Back to – =DATE(YEAR([Modified]),MONTH([Modified]),1)

    I’ll keep an eye on it, and let you know

  5. Ryan says:

    @Richard – I think the missing [] is a red-herring. It’s convenient to use to make syntax obvious but SP doesn’t seem to actually need it if field name is a single word. If you look again at that formula I suspect that its lost them again?

    What I think may be happening here (and I am speculating a LOT) is that the IMP job is
    - updating every record with something
    - hence changing the modified dates & causing the month start/end date columns to be recalculated
    - doing something funky to put the modified dates back to what they used to be.

    Interested to know how this goes for you next time you run the job again.

  6. Richard says:

    @Ryan – Your right about the [] being a red-hering, they have already gone.

    I will keep an eye on it, but I have put in reduancy ready for next month with a hidden filed which is updated with ‘Today’ as part of a workflow which runs on the list items when I modify a key field.

    Will let you know.

  7. guru says:

    hi,
    i want to show only the items which are in current year..
    how can I do that?
    help is needed

  8. Ryan says:

    @Guru – if you actually read the article you will see a formula for showing only the current year at the bottom.

  9. Guru says:

    Hi,
    I have a column
    Year and Month
    in the calculated column I want a formula for to combine both return a last date of the Year and Month,
    if Year = 2012
    if Month = 1

    I need Date type calculated column, 31-01-2012
    How can I achieve?

  10. Ryan says:

    @Guru – you would be better asking this sort of general question on a forum such as http://sharepoint.stackexchange.com/

  11. Kevin says:

    I have used the code you gave above and it is working great. Only 1 question. In the data I am using this filter against, sometimes the date field is blank. In these instances SharePoint returns a date of 12/1/3799. Is there a way to blank out the calculation of Start Month and End Month if the field it is calculating off of is a NULL value?

  12. Ryan says:

    @Kevin – you can use an IF(ISBLANK(FIELD),””,

    So then you’ve got three filters

    [Start of Month] < = [Today]
    AND
    [End of Month] >= [Today]
    AND
    [Date Field] Is Not Equal To

  13. Charlie says:

    Thank you very much, this technique is very useful!

  14. Siddhi says:

    Can we group the tasks by the task due instead of creating filters.
    If there is a way, Please will you let me know. Thank You.

  15. David says:

    Hello,

    I am having a problem trying to make the weekday formula show entries/dates of the “current week” so that the week starts on Thursday and ends on Wednesday. Right now I have it set to Start on Monday and end on Sunday which has worked out fine. I just need to figure out, based on the following formulas I have, how to make the week begin on Thursday and end on Wednesday:

    Column 1: Week Start – Calculated formula: =[WAR Date]-(WEEKDAY([WAR
    Date],2)-1) Return value: (Date and time – Date only).

    Column 2: Week End – Calculated formula: =[WAR Date]+(7-WEEKDAY([WAR
    Date],2)) Return value: (Date and time – Date only).

    View Filter: Week Start “is less than or equal to”
    [Today] Week End “is greater than or equal to” [Today]

    The formula was received from http://abstractspaces.wordpress.com/2009/05/02/common-date-time-formulas-for-sharepoint-calculated-fields/

    So If I enter items this current week Monday through Sunday, they all
    display on that view (which is good). Any items entered in before this
    week, or after this week, should display “when” that current week happens
    (which it does).

    Any suggestions or ideas or modifications to my two formulars for Start/End weeks would be extremely appreciated!

    Thank you,
    Dave

  16. David says:

    Ryan – Thank you so much for showing how to filter current weeks based on different start and end dates. The IF Statement is what was needed the most! This has been extremely helpful! Thanks again!

  17. Valentin Shtronda says:

    By some reason SharePoint does not support WEEKNUM function so I had to create my own. There are several defintions of a “week number”. I needed European one (format 21 in Excel’s WEEKNUM). Enjoy:

    =WEEKNUM(Date;21)

    –>

    =MOD(INT((Date-DATE(YEAR(Date);1;4)+WEEKDAY(DATE(YEAR(Date);1;4);3))/7);52)+1

    It assumes that you have Date column.
    If you have Year column then it becomes a bit simpler:

    =MOD(INT((Date-DATE(Year;1;4)+WEEKDAY(DATE(Year;1;4);3))/7);52)+1

  18. Jer says:

    It would seem your Previous and Next Month calculations are swapped.

  19. Jer says:

    I retract my last statement as when used with the filtered views it’s correct. It’s a little confusing to seee the calculated field for “previous month” show next month’s actual dates and vice versa for “next month”

  20. Stephen says:

    Is there any way to programme the Calendar Scope view in SPS 2007 to show next 4 ‘rolling’ up and coming weeks rather than whole calendar months, (and rather than filtering out the past items/events themselves), with the current weeks at the top? Or are those 3 scopes fixed and it’s List view only? Obviously near the end of a calendar month a lot of past dates/used space you don’t want and a manual clicking to flick between those on the next calendar month. Week view doesn’t look far forward enough….
    Thx

  21. Ryan says:

    Hi Stephen – I don’t think there are is any easy way to modify the built in Calendar view like this. Its all generated by javascript so you *could* hack that but there are is no published API so its going to be very hard.

    Can I suggest taking a look at our Planner product. It doens’t look the same as the calendar but does have a “From Today” view that allows you to do things like see from Today for the next 4 weeks. http://www.pentalogic.net/sharepoint-products/planner/planner-manual?p=configuration%2Fconfig_period.htm

  22. JulieInHouston says:

    Ryan, your excellent blog on month and week filters has been extremely helpful – thank you! But would you explain the logic of the Previous or Next month formulas? My SP colleagues and I are totally bumfuzzled by the logic of Previous and Next month Start and End periods. The filtered results are spot on, but the formulas make no sense to us. Would you walk us through the logic so we can see how they work? Current month is clear… it’s just the others.

  23. Ryan says:

    Hi Julie – Not sure what bumfuzzled is exactly but it sounds … painful!

    Keep in mind that with this method we’re doing it all the ‘wrong way round’ and getting the month of display and thats likely the cause of the confusion – maybe you’ve got it if you understand the current week formula but it might be worth reading the article again to get that part.

    So for items in previous month what we are after is a start and end date that is in the NEXT month – i.e. display a records with a date of 15th February means show it between 1st March and 31st March :-

    The formula we use is DATE(YEAR,MONTH,DAY) which creates a date from 3 numbers – YEAR, MONTH DAY.

    So for the start of the month we just need to add one month onto the date – so with date of 15th Feb we get

    DATE(YEAR(date), MONTH(date)+1,1)
    becomes
    DATE(2013,2+1,1)
    or 2013, April (3), 1st

    But for the end of the month we don’t know how many days there are right, so the easiest way to do this is to go to the start of the month two months in advance (1st April) and then move 1 day back.

    DATE(YEAR(date), MONTH(date)+2,1) – 1 <– Notice the +2 on the month then the -1 day
    becomes
    DATE(2013, 4, 1) – 1

    which becomes 1st April less 1 day or 31st March.

    The magic is that SharePoint is smart enough to know that start of a month -1 day is the end of the previous month – same when crossing year boundaries.

  24. JulieInHouston says:

    LOL! Bumfuzzled is a great word, isn’t it? It means totally and helplessly confused. Well, thanks to your explanation we are bumfuzzled no more. Many thanks!

    PS: We assume that’s a typo in your first example where April (3) should actually read MARCH (3).

  25. KatieInLondon says:

    This is excellent thank you!

    I was wondering if any one has any bright ideas about how to send an alert/summary of this to some predefined email addresses?

  26. Ryan says:

    @KatieInLondon – Not sure what you’re wanting to do exactly but we have Reminder product that sends date based email alerts – http://www.pentalogic.net/sharepoint-products/reminder

    So you could, for example :-
    - Send out email reminder alerts for all items due in the next month
    - Send out warnings for anything that is overdue

  27. Garrett says:

    I tried using the weekly formula and I am getting a syntax error. I am using SharePoint 2010 and copy and pasting your formulas. The monthly option seems to be working fine.

  28. Ryan says:

    @Garrett – hmmm – works fine for me and plenty of other people. Is your date column called due date and are you using UK/US regional settings or something else?

    Oh I see you’ve made a typo on your email address so you won’t get this…

  29. Garrett says:

    Ryan,

    Like I said the monthly method worked just fine and I have double checked all the syntax. We probably are using US settings, I will try again today and report back.

  30. Anna says:

    Hi Ryan! This is great post, thank you! Instead of displaying item for the month they are due I need the view to display the current calendar month plus 90 days.

    Adding this to my view filter:

    Start of Month is less than or equal to [Today]

    displays items from the previous months as well, I only want to see starting May 1 this month and items starting June 1 next month and so on.

    Please help!

  31. J Lee says:

    Ryan,
    Thank you for this tip, it’s works great, EXCEPT when the event falls on the first of the month AND it’s an all day event.

    I’m not sure what’s causing this bug. My solution has been to uncheck “make this an all day event” and then set the time to start at 12:00am and end at 11:59pm (exactly as it is for an all day event!), and for some reason it works. Just thought I’d pass that along.
    Regards

  32. Fenrir says:

    Hellow there.

    This was really useful for me just one question.

    How can i filter by prev month? im traying to do it as follow but the view did´t show anything.

    The columns looks like:

    Start Month =DATE(YEAR([Due Date]), MONTH([Due Date])-1, 1)
    End Month =DATE(YEAR([Due Date]), MONTH([Due Date]),1)-1)

    And the Filter:

    Start of Month is less than or equal to [Today]
    AND
    End of Month is greater than or equal to [Today]

    Regards.

  33. Ryan says:

    @Fenrir – You’ve got the formula there to show the NEXT month not the PREVIOUS month – see the formula at the bottom of the page.

  34. Fenrir says:

    Hellow again.

    Im sorry if I double post but I dont know if my last message is upload so…

    Now Im able to do the calculated columns whit the previous month, the problem is whit the filter; if i put this

    Start of LastMonth is less than or equal to [Today]
    AND
    End of LastMonth is greater than or equal to [Today]

    The view dont show anything, Im doing something wrong? could you help me please? I want to filther by the prev month.

    Thanks.
    Regards.

  35. Ryan says:

    @Fenir – look at the bottom of the page, your last message was posted and a reply made.

  36. Rushabh Shah says:

    Hi,

    I wanted to make the views which gives me alerts for daily weekly monthly recurring events.

    Can you please suggest me the formulas for that.

    Thanks in advance :)

  37. Ryan says:

    @Rushabh – there is no way to do this OOTB in SharePoint so you have to look at 3rd party software such as our Reminder product that gives date based email alerts – http://www.pentalogic.net/reminder

  38. Josh says:

    Hi Ryan,

    Awesome post! I’m trying to create a view which will show everything that was completed for the previous week, but I can’t find a “previous week” formula. Any help would be appreciated.

  39. Ryan says:

    Hi Josh – if you look at the bottom of that post there is a formula for the current week. You can adapt that by 7 days to get the previous week, e.g.

    Start Week =[Due Date] – WEEKDAY([Due Date]) +8
    End Week =[Due Date] – WEEKDAY([Due Date]) +15

    (its counter intuitive that you;re adding days to get a previous week, you have to really grok the method above to understand)

    Good luck!

  40. Mavis says:

    Ryan, your post rocks! thank you so much for your great post!!! I did that on designer and it works so well and you make it so simple to use!!

  41. Gianna says:

    Hi Ryan,

    I had the same problem as J Lee, any way to make this work with all day events?

  42. Ryan says:

    Not that I know of and its going to be very very hard if it is at all possible.

    The root of the problem is arguably a bug – of course Microsoft will argue this is a design decision ;) – in SharePoint in that normal events start/end time are recorded in the database in UTC and presented in the local time zone but All day events are STORED in the local time zone!

    This means that when SharePoint creates a WHERE clause to the database it will convert the dates for you into UTC which will pick up the normal events that were stored correctly in UTC but not the all day events that were stored incorrectly in the local time zone.

    See this post for more info, but no solution – http://stackoverflow.com/questions/1520651/sharepoint-all-day-events-behave-differently-in-caml-query

  43. Álvaro Branco says:

    That’s brilliant! Thanks for sharing it!

  44. Sander says:

    Hi,
    I’m having trouble to show the previous month.
    Can someone help me out here?

    Made 2 columns:
    1 Start previous month
    2 End previous month

    1. =DATE(YEAR(Modified);MONTH(Modified)+1;1)
    2. =DATE(YEAR(Modified);MONTH(Modified)+2;1)-1

    These columns are showing the right dates.

    But I’m unable to get it filtered for only last month.
    These are the 2 filters I’ve set:
    1. Start of prev. month
    is less than or equal to
    [Today]
    AND

    2. End of prev. month
    is greater than or equal to
    [Today]

    Looks to me they are fine. But it still doesn’t show me anything.

    Can you please help me out here.

    Thanks
    Sander

    Using SP foundation 2010

  45. Ryan says:

    What dates are your calc formula fields showing? If we’re in January and we want to show items modified in December then Start should equal 1/Jan/2014 and End should equal 31/Jan/2014. (i.e. show items from December in Jan). If you’re struggling upload a screenshot of your list (showing the data from those two calc columns) and your filter to http://imgur.com/

  46. Aabir Rizvi says:

    Hello – How can we filter all events by month? I want to create a view for each month. So January view should show all events for Jan only.
    Thanks!

  47. Ryan says:

    @Aabir – thats standard SharePoint stuff – no calculated columns required. E.g. if your date field is called [My Date] then for January you would create a new view and set filter to

    [My Date] is equal to or greater than 1/1/2014
    AND
    [My Date] is less than or equal to 31/1/2014

    (obviously change date format to match your regional settings).

    However – this will be a real pain to keep updated as you will have to create a view for each month (and year) so a better solution is to use a Filter Provider web part like our own FilterPoint web part (others are available) – http://www.pentalogic.net/filterpoint

  48. Angel says:

    Hi! Do you know any way in which we can show two months of calendar views side by side? Like, you can see both June and July and when you click the next month, it’ll be July and August. Thanks!

  49. Angel says:

    Hi! Do you know any way in which we can show two months of calendar views side by side? Like, you can see both June and July and when you click the next month, it’ll be July and August. Thanks! :)

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