Gunnar Peipman's ASP.NET blog

ASP.NET, C#, SharePoint, SQL Server and general software development topics.

Sponsors

News

 
 
 
DZone MVB

Links

Social

Filtering SharePoint calendar by Start Time

I had a problem when trying to filter WSS 3.0 calendar list by Start Time column. This column doesn't appear in filtered columns list somewhy. I found solution that smells like dirty hack to me, but at least it solved my problem. Oooh, this SharePoint is sometimes so kinky.... I'm almost sur ethere is better way to do that but if you are in hurry and need a working hack then here we go!

 

NB! This blog is moved to gunnarpeipman.com

Click here to go to article

Comments

Mike Fairley said:

Yeah - ain't that weird.  Why does SharePoint have these annoying litle idiosyncrasies.  Nice work around though.

# October 29, 2007 11:25 PM

Yair said:

This doesn't work when you expand recurring events.

Yair

# April 21, 2008 5:46 AM

chris said:

This workaround worked great - I am starting to develop a love/hate relationship with Sharepoint though.

# May 29, 2008 7:35 AM

DigiMortal said:

It seems to me that you can do many things using Web Parts because you can use API and CAML. Laying purely on SharePoint browser based environment is not usually enough.

# May 30, 2008 1:49 AM

Vin said:

Any idea how to overcome the issue with recurring event?

# June 1, 2008 9:55 AM

Sonnekki said:

To correctly deal with recurring events, make sure that your filters are correct; get the "Begin" column from both sides:

Say you want a window from today to 30 days in the future.

Here's what the filter would look like:

Begin is Less Than Or Equal To [Today]+30

AND

Begin is Greater Than Or Equal To [Today]

# August 4, 2008 10:35 AM

Jason said:

When using recurring events the start date defaults to the day the event was set to start. While the "Start Time" column displays the different dates as they progress, if you do the calculated field on "Start Time" it will only return the start time of the entire recurring event. So if you told the recurring event to start on 8/1/08, the Start Time may show 8/5/08 (today) but the calculated column will still show 8/1/08 regardless. I don't see how filters would fix this.

# August 5, 2008 4:02 PM

Fred said:

Yeah,,,what Jason said.

Desperately seeking solution.  

Shame on MS for this one!

# August 20, 2008 2:41 PM

Steven said:

To filter a calendar with recurring events:

Open Calendar list settings.

Add new column called (by example) End.

Choose "Calculated" as a type of new column.

In the Formula field insert the following formula:

=[End Time]

Select Date as data type of new column.

Clear check from "Add to default view" checkbox.

Save the new column.

When filtering the view,

Select the 'End' column

Select "is greater than or equal to" option

Type: [Today]

Explanation:

All events have an end date associated with it.  So, if the recurring event has a recurring date set for a future date, then that event will continually show until the end date of the occurance has past the current date.  There is no reason to create the 'Start' column for this example.

# August 29, 2008 1:03 PM

Leon Bryant said:

There is aslo weirdness in the "All Day Event" function. The calcualated fields do not work correctly. any thoughts?

# October 6, 2008 6:36 PM

Gabe said:

Steven,

Your method should work. Unfortunately something in the RecurrenceData of the events does not always allow this to function properly.

For example when I tried your method, most of my recurrences appeared fine. one recurring event appeared 3 times, then one recurrence did not. the two recurrences appearing in the filter had the EndCalculation = EndTime (Date Only). The last recurrence which was not showing had EndCalculation = 10/15/2085. I have had other end dates of 10/31/2012 and 2099. This happened with 3 other recurrence series which also had bad End Times.

It sounds crazy but Sharepoint is randomly instatiating wrong End dates into the RecurrenceData XML.

# October 30, 2008 10:13 AM

DK said:

Hi

Does anyone have a solution yet for the recurrence issue?

# November 3, 2008 8:03 AM

Maleware said:

When I try to Save the new column. I get this error message ...

"The formula contains reference(s) to field(s). "

Any ideas gratefully received!

# February 16, 2009 10:18 AM

dab0816 said:

Still having the same recurrence issue

# February 26, 2009 8:55 AM

Tim said:

I don't have a solution to this problem, but I can attest to how korky SharePoint is...

I have a view setup to filter by a keyword in the description field for calendar events (which are recurring).  If I add criteria to filter by End Date, using AND operator, I actually get some items that did not show up when only filtering by description field.  Makes no sense.  

For you logic buffs, I'm starting with a set A, filtering off items (by description) to get a sub-set B; then filtering B (by date) to get set C.  Therefore C should logically be a sub-set of B, but what I'm saying is that it is NOT, which is very alarming, and MS really needs to do something about SharePoint's disparities. It's wasting my time and I'm sure many others.

# March 26, 2009 4:45 PM

Tim said:

I may have figured something out here:  

When creating the view, choose "Standard View, with expanded recurring events"

This will list each instance of a recurring event, from 'today' on.  If you only want the first 10 items starting today, just set the 'limit' to 10.  

I've tried this out, and it seems to be working.

# March 26, 2009 5:35 PM

Brian said:

Seems like since the time you enter in the event is linked to a date.  Even though you're not putting in a date.  

MS should have fixed this by having "Date", "Time", and "Date Time".

you could create a cloumn "Time" and have the dropdown have a list of times they can choose from.  The problem lies in showing recurring items from today on.

sorry i added nothing to this.

# April 7, 2009 3:12 PM

Kelly said:

WSS v 3.0 does not allow you to filter using Today/Me etc

Has anyone found an alternitive that works on WSS v3.0?

# May 13, 2009 8:47 PM

cris said:

can be possible to compare in workflow two date/time data type  (where one is calculated )

Action such as

If duedate> create

Condition

send email

# June 11, 2009 10:55 PM

cris said:

can be possible to compare in workflow two date/time data type  (where one is calculated )

Action such as

If duedate> create

Condition

send email

# June 11, 2009 11:13 PM

Jamie said:

Many thanks for this solution, it has been taking up a lot of my time.

# July 23, 2009 4:08 PM

Rajesh said:

My calendar has mix of recurring and regular events. I had to show only a filtered list in default.aspx page and hide all past events. When I applied a filter to hide "End Date < Current Date", recurring events stopped showing.

Workaround filter...

Start Time >= Current Date (OR)

Recurrence = Yes

It works for me. It just shows one line for the recurreng event starting on 5/1/2009 thru 9/20/2028. I am fine with this solution. Hope this helps someone. Please note the condition is a [OR] not [AND]

# August 6, 2009 4:13 PM

Mhegz Flores said:

i dont have any backround using microsoft sharepoint so i wont be able to share any formula r what so ever here. But still, i want to extend my gratitude to all of your advice and it helps me a lot.. THANKS! :)

# September 28, 2009 2:25 AM

Shawn S. said:

So when you reference [Start Time] it is referencing Start Date in the events Recurrence settings. Not the actual start date of the event on each day of the recurrence.

Any ideas on finding the real start date for the recurrences.

# October 22, 2009 3:17 AM

Mierdin said:

Creating a View that expands recurring events is great, but all that does is let us know how many occurences in the recurrence Sharepoint can see at a given time. Each occurence still has the start date of the first occurence and an end date of the last occurence. So this doesn't help.

Does anyone know of any way to emulate the algorithm Sharepoint uses to determine which days a recurrence falls on? Dealing with an operation like that client-side seems to be the only way to go here.

Serious Microsoft fail on this one, btw.

# December 8, 2009 2:28 PM

Gary said:

Trying to make a view of only events happening Today.

I have my filter working for normal events and All Day events but not Recurrence.

Seems like this should be a basic "Out of the Box" view, but is very difficult to make on my own...

# December 18, 2009 12:29 PM

Azwarian said:

Awesome! This has saved my big time :)

Thanks a million mate

# January 12, 2010 6:47 PM

SS said:

When tring to use [Today] in the filter, getting error massage as " Filter value is not a text string". Can anyone help ?

# February 18, 2010 3:41 AM

Shaun said:

You could edit the web part in SPD.

For simplicity I filtered by "Created" equal to or greater than "[Today]".  Then I openned the view in SharePoint Designer and replaced "Created" with "EventDate" which is the internal name of the Start field.

# September 9, 2010 3:11 PM

Sofia Khatoon said:

Very good idea. This is exactly what I was looking for. Thanks for your work.

# January 12, 2011 1:49 PM

Mark said:

I would like to reask Maleware's question that went unanswered:

When I try to Save the new column. I get this error message ...

"The formula contains reference(s) to field(s). "

Any ideas gratefully received!

# September 18, 2011 5:20 PM

Ryan said:

@Mark and @Maleware - The error "The formula contains reference(s) to field(s). " is because you did not choose "Calculated" as a type of new column in step 3.

# October 26, 2011 12:16 PM

Robert said:

To Mark and Maleware--though it may be way too late for you--it may work for others having the same issue.

I found the same error.  But I found that I was referencing the column "Start Date" i.e. my formula said "=[Start Date]"and I had changed the column name from Start Date to something else.  If you get the error, make sure that you are calculating to a column that actually exists.  Once I showed it equal to the correct column, it duplicated the date and it was also viewable.

# May 9, 2012 8:31 PM

Matt said:

Hi All,

I am trying to set a validation for the calendar were it accepts resource booking only during "working hours" i.e morning 8:00 am to 5:00 pm

I tried to using

=[Start Time]> "08:00:00 AM"

=[End Time] < "05:00:00 PM"

I tried different formats and combinations. But it is of no use. I like to know how to use the time format to validate this scenario.

Thank you

Matt.

# May 16, 2012 1:13 PM

JD said:

I have followed your instructions to the letter on our SP2010 install creating a calendar. However I get this error: "Filter value is not in a supported date format."

My new column name is "Begin Time"

It is a calculated field =[Start Time]

Date type is Date and Time

Date and Time format is Date & time

I cleared the check from "Add to default view" checkbox

Next, I created a Standard View, with Expanded  Recurring Events.

I chose to display only two fields: Title and Begin Time.

Then, I set the filter to "Show items only when"

Begin Time >= [Today]

When I press OK, the error message displays

"Filter value is not in a supported date format."

I've spent too much time on this. :(

# July 19, 2012 3:56 PM