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
@Dan – haha, cheers!
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
@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?
@ 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
@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.
@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.
hi,
i want to show only the items which are in current year..
how can I do that?
help is needed
@Guru – if you actually read the article you will see a formula for showing only the current year at the bottom.
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?
@Guru – you would be better asking this sort of general question on a forum such as http://sharepoint.stackexchange.com/
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?
@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
Thank you very much, this technique is very useful!
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.
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
@David – Check out this new post showing how to filter by current week – http://blog.pentalogic.net/2012/09/complete-guide-to-filtering-sharepoint-lists-by-the-current-week/
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!
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
It would seem your Previous and Next Month calculations are swapped.
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”
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
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
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.
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.
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).
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?
@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
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.
@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…
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.
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!
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
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.
@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.
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.
@Fenir – look at the bottom of the page, your last message was posted and a reply made.
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 🙂
@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
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.
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!
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!!
Hi Ryan,
I had the same problem as J Lee, any way to make this work with all day events?
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
That’s brilliant! Thanks for sharing it!
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
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/
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!
@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
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!
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! 🙂