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

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


[Today] <= Last Day Of Month

So looking at this in graphical form we have :-



  • 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]
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)


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.


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

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

  1. D Sherwood says:

    I have 90 employees who are responsible for taking approximately 17 training classes once a year. I have worked for weeks trying to figure out how to configure the list to provide views for what has been completed and what is due. HELP! I’m tracking by employee with a colum for each course and their completion dates. I need to be able to either color code when someone is about to have some training due or be able to setup an alert.

  2. Ryan says:

    Mr Sherwood – I’ve sent you an email asking for more details as this question is a bit too in-depth for a blog post comment discussion and we do have some products that will be able to help you – Reminder and Highlighter

  3. D Sherwood says:

    Where I work, there are about 90 people (give or take). Each one is required to take “x” number of training classes annually. I have a list where each person is listed on the Y axis and each class is a column. When users complete the training they go in and put a date in the column of the training they’ve completed. I’d like to be able to color code the cells or send an alert to folks who are within 30 days of having that annual training (course/class) come due. Many thanks for your quick response. Oh, and the platform is SP 2010.

  4. Ryan says:

    D Sherwood – I can’t help you in these comment sections – your question is just too detailed. As you can see from my previous reply I’ve emailed you from our support address – please reply to that email and I’ll do what I can to help.

  5. Michael says:


    I have managed to get the initial section working i.e. Start Month End Month but i am trying to create a new view to only show those tasks for the next month so followed the formula:

    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

    But not sure what the filter needs to be to only show this criteria, please can you help.


  6. Ryan says:

    @Michael – Erm, if you read the blog post again it does show how to set the View filter.

  7. Alan says:

    Hi Ryan,

    I have tried using these formulas and others from your cheat sheet on our SharePoint 2013 server and nothing works. I either get an error or nothing. Here is an example formula and the error:

    =DATE(YEAR([Created]), MONTH([Created]), 1)
    The formula contains reference(s) to field(s).

    Do you know of a configuration setting that might be wrong?

  8. Ryan says:

    Alan – you’re trying to use that formula in the Default Value box, NOT in a Calculated Column – check the article above carefully.

    As an aside – the reason why you can’t use it in a Default Value is at the point that formula is worked out its a brand new record – it doesn’t have a Created Date or a ID or anything else as it doesn’t yet exist.

  9. Anthony Pizzuto says:


    Thank you for this information. Very helpful. Curious though, I have multiple milestones every week and I’d like to show a breakdown of ALL milestones for a given week, I tried the following in a calculated column:

    =DATE(YEAR([Column 1],[Column 2],[Column 3],[Column 4],[Column 5],[Column 6],[Column 7],[Column 8]), WEEKDAY([Column 1],[Column 2],[Column 3],[Column 4],[Column 5],[Column 6],[Column 7],[Column 8]) +1

    However on submit I get “Sorry, something went wrong” The formula contains a syntax error or is not supported.

    Any idea what I’m doing wrong?

    Thank you!

  10. Ryan says:


    Thats not a valid formula as most of the functions like DATE, YEAR, WEEKDAY etc can only accept one column not multiple ones.

    I don’t think you’re going to be able to do this with multiple milestone columns as you SharePoint filters don’t support the complex order of filters – like (Start1 AND End1) OR (Start2 AND End2) … – that you would need to pull this off. Sorry it wasn’t better news!

  11. Anthony Pizzuto says:

    No worries. Thanks for the quick reply!

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