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