Filter by “Events this Week” in a SharePoint Calendar
We’ve been working hard tweaking out the SharePoint Community Calendar since its inaugural rollout last week. One of the features we added today was a new tab that filters for all items within the next week, or seven days. As I created the filter, I realized a lot of new SharePoint Site Managers might not know how to use “Today” as a math function when filtering. Let’s take a look…
When I first started to work on the solution, I thought “No brainer. I’ll use the Start Date column as a filter and set things up.” Problem is… there’s no way to use the Start Time column in a default filter in SharePoint. It’s available in the Sort functionality, but not in the filter functionality. Say what?
How in the world can I create a seven day filter when the column I need to filter by isn’t available?
Ah, that’s what the calculated column is for! Create a calculated column that points to the Start Time column, suck in the value and use it to create the filter in the new column. Name the column Start Date, and we should be good to go for filtering.
Now that there’s a column to filter against, I need to create the filter based upon today’s date. The filter should say, “Display all events that are equal to or greater than today, and make sure they are less that seven days from now.” That should set us up with a dynamically generated display of the events available for the coming week, based upon today’s date.
Alright! That worked really well.
“Hold on a second!”, I hear some of you saying. I’ve heard that the “Today” trick is really lame and doesn’t work. It just takes the value of “Last Modified” and puts it in there. Well, you doubters are correct in one sense: Today DOES NOT WORK in a calculated column. But look again at what we did above…
Today is used as a filter, not a value in a calculated column. Yep, it’s going to work. Hit the SharePoint Community Calendar tomorrow morning first thing, and see for yourself.
Happy filtering!
Update: People are requesting the ability to include recurring dates in the filtering. This is not possible for reasons explained by Ryan in the comments. — Mark
Thank you! What an elegantly simple solution to a problem that’s been vexing me for ages!
And what a simple, elegant comment. Much appreciated. — Mark
It’s now March 25th, 2010 and the view has automagically updated itself… Alright!
I have noticed that this calculated date column is off by one day on an All Day Event. For example if I have an all day event for March 25/2010, the Start Date calculated column will display a date of March 24/2010, and therefore my view for events this week is excluding some events. Is there a solution to this?
I ran into this same problem when I implemented a solution similar to this for a company PTO calendar. It seems that the calculated column “calculates” an All Day Event according to Greenwich Mean Time (GMT). Since our location is in the Central time zone I had to add six hours to my calculated start time! What I was seeing was that when I set an all day event start date to March 25th for example, my start time would begin on 3/25/2010 at 12:00 AM but my calculated column would show that it started on 3/24/2010 at 6:00 PM (six hours earlier).
For my solution I had to set my calculated “Start Time” to equal “[Today] + .25″. The value “.25″ is equal to 6 hours because 6 hours is 1/4 of a day (24 hours).
Hope that helps!
Sorry, I entered my calculated column wrong. I set my calculated start time to equal “[Start Time]+0.25″.
How can I get the calculated Start Date to equal the actual meeting date for recurring calendar entries? =[Today] is giving me the first date in the series.
Thanks!
Amy
Is there a way to work this for a calendar full of recurring events?
Not sure. I haven’t played around with it for recurring events. Over the next couple weeks, I’m rebuilding the calendar, so I’ll take a look. — Mark
Have you had a chance to look at recurring events? This solution is perfect except for most of my calendar events recur and I do not think this would work for them.
Thank you very much!\
David
Sign In to Vote
0
Sign In to Vote
I have a custom view which is supposed to check that the Start Date is greater than or equal to [Today].
But [Today] does not check the Date + Time!! Why not? I find it very hard to believe that this is by design, how can they leave out such a basic feature. Seriously!
I tried using [Now] but its not allowed.
How can i filter my view by Date + Time?
I’m using Sharepoint 2010
Did you set your calculated column (Start Date) to equal “Date and Time”? By default, calculated columns are set up to be a “single line of text” column. If your calculated column is single line of text, then you cannot compare it to the [Today] value.
its a event list.. its a out of box feature. i just used the even list, there the column is not calculating its value.
but i have fixed the issue by overriding the querystring in that webpart.. with CAML query .. now its working fine..
Thanks to everyone who posted about the GMT date filter issue…brilliant!
I am really struggleing with having to choose between seeing recurring items OR having a grouped view of events. (yes, am trying to develop a simple “This week at…” that is organized by event type. I want a grouped view of events that identifies individual instances of recurring events
Any new insights on this would be so welcome.
SharePoint 2007
Thanks a ton! This was exactly what I was loking for. You cracked it so well to solve the Today problem.
There’s a good reason why the Start Time isn’t in the list of filterable fields for event lists – as others have found its because of recurring events.
Just as with the Zombie (i.e. it will not die) fake Today column trick (in calculated columns, [Today] has been perfectly valid in filters from 2007 on) when you’re fooling SharePoint into doing something that its tried to stop you doing there are probably going to be consequences.
Mark – maybe its would be good to put a disclaimer on this article that it will only work if you don’t use recurring events in your calendars list? Most people will not see the comments below, so try it and be left annoyed?
Agreed. I updated the article. Thanks. — Mark
It is possible with recurring events, although I have yet to come up with a way for it to display this occurrence’s event versus the start of the series: http://social.msdn.microsoft.com/Forums/en-US/sharepointcustomization/thread/d7be2bea-bab9-43a8-a7f6-b934f3b529bf
@Joel – I am not following you. “It is possible” and “I have yet to make it work”?
Is it possible to get multiday events to show on this list?
hi
can anyone tell me how to do this in sharepoint 2003
thanks
jehan
I tried this and when I clicked the filtered view, SharePoint just displays a blank page
I set the filter up as described and when I view it SharePoint displays a blank page.