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
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.
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
http://www.pentalogic.net/sharepoint-products/reminder
http://www.pentalogic.net/sharepoint-products/highlighter
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.
Cheers!
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.
Hi
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.
Thanks
@Michael – Erm, if you read the blog post again it does show how to set the View filter.
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?
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.
Hello,
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!
Anthony
@Anthony,
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!
No worries. Thanks for the quick reply!
Hi Ryan,
After combing through just so many blogs, must say this is a very helpful & well written post.
I am trying to show the events happening in the calendar which meet the below conditions, –
1. The even has started and is running now
2.Event upcoming in the next two hours
The calendar I’m working with has recurring events. Please can you help ?
Cheers !
@Pujs – Thanks. You can’t do what you’re after with SharePoint unless you write lots and lots of custom code. Sorry! (See “this doesn’t work on Event lists with recurring events” in post above)
Thanks Ryan for getting back.
I’ll probably have to stick to a view where we show events which are occurring now.
Cheers !
Ryan,
Thanks for a great post. I am having trouble getting this working. I am using a SharePoint 2007 contact list and have a date and time field labeled birthdays in the format mm/dd/yyyy. I have followed your instructions to the t and can get nothing to show up in the filtered list. Testing with a view that shows birthdate, start of month, and end of month fields shows that the calculated fields are working properly, but the final birthday view is empty. Any thoughts on what could be causing this?
Ryan,
I think I have figured out what is going on (at least in part). I bulk added 160 records through the “Edit in Datasheet” view. Those birthdays don’t show up in the filter I have created. If I manually add a record, the birthday shows up. Any thoughts on this?
I’ve no idea – at a guess have you checked the calculated columns to see if they have the right info in them? Perhaps they didn’t get calculated when you did the bulk insert.
Ryan,
Thanks for the reply. Actually I did some more testing on this and discovered that if I add a birthday with the current month AND current year(2015) it works as expected. How would I change the start of month and end of month formulas to look at only the month and not the year?
Just read over your very good instructions and realized that the filter is doing exactly what it is supposed to do. I am a noob!
Ryan,
Any thoughts on how to filter just for the current month and not the year?
Scott – I am not sure how you would do this. I think your best bet is to ask on a forum like http://sharepoint.stackexchange.com/ but explain clearly in your question what your after. E.g. I think what you want the view to show is for February, all items in any year for the month of February. Good luck!
Hello Ryan,
Using your same logic would it be possible to create a filter on time?
Filter Requirement: Want to create a view to see from 3pm Yesterday to 3pm Today.
I came up with this calculated column MetricsTime:
=TEXT(Modified,”h:mm:ss AM/PM”)
Then applied this filter:
Modified Greater than or Equal: [Today]-1
AND
MetricsTime greater than or equal: 3:00:00 PM
Issue: This filter will show me everything even past 3pm.
Any suggestions or thoughts?
Hello Ryan,
Quick question, can this logic be applied to Time? I’m lookig to do a filter (daily view) from 3pm yesterday to 3pm Today.
Steps taken:
1) Created calc column (Metric) with this formula to:
=TEXT(Modified,”h:mm:ss AM/PM”)
2) Created Filter:
Column Modifed – Greater than or equal to: [Today]-1
AND
Column Metric – Greater than or equal to: 3:00:00 PM
Issue: This will show me everything from Yesterday starting at 3pm but will not stop at 3pm TODAY.
Any ideas how to get specific daily view 3pm yesterday to 3pm Today.
Thank you for your time.
Hi there,
My agency just migrated from SharePoint 2007 to SharePoint 2010. I’m trying to configure a Calendar webpart to reflect only upcoming events in a migrated list to match what folks were used to seeing in the 2007 site (all events occuring today and greater). I’m reading that in your post above that none of this will work with recurring events.
Are there any options to capture BOTH recurring events and one-time events that will occur today forward (or even just from tomorrow forward)?
Thanks!
Hi Ryan,
I applied your technique to create a view which displays events from the previous month only. It works really well, except that the view filter still picks up events whose start time is the first day of the current month.
The calculated column for the beginning of last month uses the following formula:
=DATE(YEAR([Start Time]),MONTH([Start Time])+1,1)
The filter for the view is set up thusly:
Start Prev Month
is less than or equal to
[Today]
End Prev Month
is greater than or equal to
[Today]
I think that is pretty much how you had it laid out. Any ideas where this went wrong?
@George – check your end of month formula – you’re missing a -1 on the end in my example the formula age “End of the month” and yours gave “Start of next month” hence the error.
Hi Ryan, thanks for responding.
My end of previous month formula does include a -1, as below:
=DATE(YEAR([Start Time]),MONTH([Start Time])+2,1)-1
The formula quoted above is for the start of the previous month.
Thanks.
@George – if you show the Start/End calculated columns in a view – what dates do you see?
@Ryan: That turns out to be a very interesting question. The answer depends when the request was made. Using the previous month view that I created using your instructions, the columns actually display start and end dates for the current month. Yet the view works! If I look at the calculated column dates in a test unfiltered view, it shows the start and end dates of month AFTER the request was made.
@George – yes thats the point! To do this you
turn the problem on its head
so to show the previous months info we’re actually figuring out the dates in the NEXT month that this should be shown. Best to grab a coffee and read the article again and hopefully will become clear!
Hi Ryan,
Great post! Thanks! But I am experiencing one issue with the current month view. When I created an event on the first day of the month and set it “All Day” event, then this event would not show in the current month view. When I switch the event to a specific time then it worked. Any idea why?
@Chenewi
* If you create an all day event on the 2nd of the month does it work? What about the end of the month?
* What time zone is your site set to (regional settings)
@Ryan,
2nd of the month, last day of month or others all work. Only when first day of month and “all day” event doesn’t show.
I am in EST.
Oh – and what version of SharePoint are you using? (2007/2010/2013)
@Ryan,
We are running sharepoint 2010.
@Ryan,
I found out this is just a problem with yearly recurring event. As sharepoint put the end date to be year 2164 so “end of month” value won’t work as designed. Thank you.
Hmm, I keep getting syntax error for the End of Month formula. I’ve typed it exactly as written.
=DATE(YEAR([Review Due Date]), MONTH([Review Due Date])+1,1)-1
Never mind, got it working. I must have included a space or something. Thanks for the article!
Hi Ryan,
Great post, exactly what i was looking for.
While the approach for the current month is working fine, it isnt working for the previous month. I have tested calculated columns, they are returning the correct values, but I dont see any items there. I have used below Filter —
Previous Month First Day [Today]
Please advise.
Thank you,
Richards.
Richard – can’t see your system but you MUST have missed something in the filter or calc column.
Thanks, i hava a calendar list, but when i am trying to create the first calculated column =DATE(YEAR([FechaCumple]), MONTH([FechaCuple]), 1) i have this error
Error
Sintaxis error not allowed.
Can you help me please.
La fórmula contiene un error de sintaxis o no se admite.
Más información sobre la sintaxis para fórmulas.
Solucionar problemas de Microsoft SharePoint Foundation.
@Evelyn – if you’re using the Spanish version of SharePoint you need to separate the different parts of a formula with a semi-colon (;) instead of a comma (,)
This will not work if the threshold is reach at 5,000 or the default threshold.
@Andy – of course not (unless the two fields are indexed) – having more than the LVT (5k by default) in any view in SharePoint is problematic – it doesn’t just apply to this method.
How about filtering by current Fiscal Year and last Fiscal Year? Fiscal year starts in April rather than calendar year January.
Ok, answer came to my mind before the comment has been moderated and published for answers.. To get current Fiscal Year filter use:
StartFY: =IF(MONTH([dueDate])>3,DATE(YEAR([dueDate]),4,1),DATE(YEAR([dueDate])-1,4,1))
EndFY: =IF(MONTH([dueDate])>3,DATE(YEAR([dueDate])+1,4,1)-1,DATE(YEAR([dueDate]),4,1)-1)
To get a filter for the Previous Fiscal Year then use:
StartPrevFY: =IF(MONTH([dueDate])>3,DATE(YEAR([dueDate])+1,4,1),DATE(YEAR([dueDate])+2,4,1))
EndPrevFY: =IF(MONTH([DueDate])>3,DATE(YEAR([DueDate])+2,4,1)-1,DATE(YEAR([DueDate])+3,4,1)-1)
Hi Ryan,
Thank you for this post! This is really helpful. Please forgive my comment has aleady been made. i looked but did not see another of it’s kind.
Is the formulas for Next Month and Previous Month mentioned in your blog post possibly tranposed?
In my situation I used the Next Month formula, but got last month’s date. I checked the Previous Month formula example, in the blog, which seemed correct for next month – so I exchanged the two formulas and it worked great.
Thanks,
Rob
Hi Rob – they are actually the correct way around. The key part of this technique is that is the opposite way around to what you would intuitively assume. If a record has a date in January and you want it to appear in a “Previous Month” view then the start/end calculated dates that you are filtering on have to be the *NEXT* month. I.e. in Feb you show Jan’s record.
I am having a similar problem as Chenwei. I have two calculated fields for beginning of the month and end of the month.
Beginning of Month
=DATE(YEAR([Start Time]),MONTH([Start Time]),1)
End of Month
=DATE(YEAR([End Time]),MONTH([End Time])+1,1)-1
End of month is working great. Beginning of month works fine for any day except all day events on the first of the month. For whatever reason the Month function is returning the previous month for those dates.
Start Time = 2/1/2016 12:00 AM
Beginning of Month = 1/1/2016
Anyone have any ideas? We are using SharePoint 2013 and are EST.
Thanks,
Adam
I was wondering how you can apply this filter for the current month and the next month at the same time. So I want to see 2 months together. Any ideas?