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