(Updated: 3/23/2011: filter was wrong, is now correct)
Background:
So I am working on my pilot of the company intranet, and I created a corporate holidays calendar. I wanted to display on the home page only the title and date of the events that were upcoming within the next 60 days. I tried to filter by Start Date, but it doesnt display as a filterable column. I searched and found an old post with some syntax and tried it out and it worked.
How To:
If there is a calendar, and you want to create a view where only events from today up to the next 60 days (or how many days out), here is how you do it:
Add a column to your calendar. Name it “Date” and make it a calculated field. Add “Start time” to the formula, and select “add date only”. What this does is it creates a column that is filterable, because Start Date is not, and automatically populates the content of this column with the start date of the event.
Next, create a new view for the calendar, name it “Upcoming” (or whatever). Select whatever fields you would like to display. If you are creating a view to show on another page, like the home page, and you just want a quick list of upcoming events, you might want to just have title and date fields display.
Under “Filter”, select:
Date
is greater than or equal to
[Today]
and
Date
is less than or equal to
[Today]+60
What this does is tells SP to only display events with Date set to today or up to 60 days from now.
Make sure that wherever you want to see this view, you actually select this view in the View drop list.
(Update) Unless you add the second filter, the query will continue to show old posts. 3/23/2011: I had the filter set wrong, the filter should work correctly now.
SharePoint for Designers 
Thank you for this article. I was wondering how to do this and knew there had to be a way to do it. It’s stupid that MS didn’t build this in.
Thanks again!
Thanks for this post. One note: If you want for example to have the list display events taking place over the next 60 days, you need to set both the lower and upper bounds of the filter, i.e. (Date is greater than or equal to [Today]) AND (Date is less that or equal to [Today] + 60).
A quick question. I’ve started using the inbuilt ‘IT Helpdesk’ task list of Sharepoint 2007. Works a treat. I played around with the views so it displays the normal ‘Current IT Issues’ List and now a second view below that that shows ‘Completed’ Jobs. So when you edit a current job and mark it complete is dissapears from the ‘Current’ list and then appears on the ‘Completed’ List.
So far so good.
But what I’d like to do is filter the ‘Completed’ list so that it only shows jobs I have marked completed in the past …oh lets say 14 days.
Would an adaptation of your workaround work? I have a ‘Date Completed’ column and I have tried filtering by that column by entering ‘greater than or equal to – [date] +7’ and variations of that but it fails.
Any idea’s?
David,
you need to put:
less than or equal
[today]-14
Hey Michelle,
I applied your trick and, after a tweak, it works great.
It seems that, for an “all day” event, the Calendar sets the Start Time and End Time of the event to 12:00am and 11:59pm respectively for the given date. For some reason, if I calculate the new Date column from Start Time, it actually sets Date as the day before the event. i.e: Date gets set to 8/28/08 for an all day event on 8/29/08… My fix was to just calculate Date from End Time. Works great.
Great blog by the way, I’m sure I’ll be checking back often:)
BrianD’s suggestion doesn’t work for dat/time entries that span more than a day. I have a mixture of those and all day events. Anyone know how to include the default start and end fields in the filter drop-down rather than using calculated fields?
This is great info. I’m wondering, how did David create the two views? I’m looking to do the exact same thing…. only with three areas:
To be Written:
To be Approved: (actived by a yes in the written column)
To be Posted: (activated by a yes in the approved column).
Then I want to use the trick explained in the article above to create a calendar reading from the “posting date” column.
Can anyone get me started with what list/filters to use?
Thanks in advance!
one other thing… I’d love to add workflow too…
I’m curious to know if this equation can include “Active” dates. Because this equation only will show the items that started between those dates. I’d like to see it include items that are spanning from before the start date. Any thoughts?
Thank you so much for posting this information – it gives me hope that we can implement a simple work-around that our clients will be happy with. If I can get this to work, we can template it easily enough and be done with the issue.
Unfortunately, this doesn’t actually work when I try it. When I create a new calculated column, there is no “Start Date” to choose from (and I can’t manually enter one). The closest thing that I can find to use is “Start Time” – which looks as though it will work.
For some reason when I actually display that new column in a view to troubleshoot why it’s not displaying what’s expecting, the date for this new column shows today’s date.
Has anyone seen this? I’ve tried redoing this several times in case I selected a different option unintentionally, and haven’t been able to figure this one out.
Help would be very much appreciated. For now the quest continues. 🙂
i have a requirement to display date that spans more than a day in a single column. anyone have any idea how could i do that. thanks
This is exactly what I was trying to do! Thanks!
I am using this exactly as it says…but SP will not take the [today]+7. Error says “filter value is not a valid date”. One small difference…where you say select “add date only”, i dont have that option. SP will take [today] fine, just will not take the +any number. I am using the correctly..have tried it several ways. Help..?
I kinda stumbled upon my own solution, so I dont claim to be an expert. This is my filter:
Date
is less than or equal to
[Today]+60
with Date being a calculated column with the formula:
=[Start Time]
with data type:
date and time, date only
This is exactly what i have in there…but get the error msg on the [Today]+60…SP says that is not a valid filter parameter…?
I have no idea why you get an error-maybe its your setup? I use MOSS with publishing turned on…. There seem to be random hick ups throughout SP, hopefully the next version will address some of these.
I also received the same error message as Rodney. I spent about an hour before giving up on it all together. However, I went to the view for which I was trying to establish this setting, and found that the filter had worked despite the error message.
Disregard my last post – sorry. I am getting the error message still and read my display incorrectly. Sorry!
To solve the problem of multi-day events, you’ll follow the proscribed method of setting a calculated date field, but do it once for start date and once for end date (so you have two calculated variables.
Then, when creating your view, set the following filter:
Start_Date_calculated) =[TODAY]
Thank you! This has worked perfectly for me 😀
Thank you! This has worked fine
I got the same error but then realized it was because I had a space between the + and the number . . . . no spaces!
Also, is there anyplace a newb like me can get a full rundown on the date functions?
@Inga – you can’t have any spaces in the filter calculation [Today]+XX
If you need to do a filter for a calendar month/week (rather than rolling 30/7 days) then this post may help
http://blog.pentalogic.net/2009/11/howto-filter-items-current-calendar-month-view-sharepoint/
Great!! Thanks alot… But: This only works for non reoccuring items. If the start date is in the past but it’s still reoccuring in future, it won’t appear… I tried to figure out a way but had no luck so far.. If anyone has solved this problem, please post 😀 This would make my calendar view perfect then 😉
Yes! I am strugging with the recurring event issue as well. The [Start TIme] for all events is the SAME as the first event. Which is not very helpful at all.
Anybody got any ideas?
Thanks,
Amy Young
Thanks for the post on date filtering in SharePoint. Very useful!
Nice! It works for me but it will not show recurrence meetings/agreement. How to fix? Thanks
Awesome article! We are one week away from launching out SharePoint site and you saved the day!
This does not work for reccuring items. If the start date is in the past but it’s still reccuring in future, it won’t appear… I tried to figure out a way but had no luck so far.. If anyone has solved this problem, please post.