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!
So, let’s go step by step:
- Open Calendar list settings.
- Add new column called (by example) Begin.
- Choose "Calculated" as a type of new column.
- In the Formula field insert the following formula:
=[Start Time] - Select Date and Time as data type of new column.
- Clear check from "Add to default view" checkbox.
- Save the new column.
Now you should be able to use the column called Begin instead of Start Date when you need to filter calendar list.
Yeah – ain’t that weird. Why does SharePoint have these annoying litle idiosyncrasies. Nice work around though.
This workaround worked great – I am starting to develop a love/hate relationship with Sharepoint though.
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.
Any idea how to overcome the issue with recurring event?
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]
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.
Yeah,,,what Jason said.
Desperately seeking solution.
Shame on MS for this one!
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.
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! :)
There is aslo weirdness in the "All Day Event" function. The calcualated fields do not work correctly. any thoughts?
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.
Hi
Does anyone have a solution yet for the recurrence issue?
This doesn’t work when you expand recurring events.
Yair
The solution mentioned above, won't resolve the problem exposed. If you analize further, you'll be able to notice that in sharepoint the recurrence dates are only shown (in allitems view), but they are not really stored as an array of dates (this is my opinion). I believe this is the reason why the date field is not available for filtering. The real value for this field is only the initial recurrence date (in start date) end the final recurrence date in (end date). I really believe it is required a patch from microsoft for this problem to be solved. If someone has a solution, is welcome! Thank you all.
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!
Still having the same recurrence issue
its urgent pls any body have code/url pls send me
my mail id murali525@gmail.com
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.
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.
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.
Hey Tim,
Thank you very much! You suggestion does most of what I was wanting to achieve:
I wanted a view of just the events occuring in the next 2 weeks and to not show "All Day Events" which ended yesterday (they get calculated as "Ending" the day after the last "All Day"). This Almost does it:
1. Copy "Start Date" to "Starting" and "End Date" to "Ending" fields
2. Create a "Standard View, with expanded recurring events"
3. Filter the view for:
All Dave Event = Yes
AND
Ending > [Today]
OR
All Dave Event = No
AND
Ending >= [Today]
4. Limit items to 5
Remaining issues:
– the "Starting" date of recurring events remains the date the recurrance was created
– Is a view of the next 5 events, not the next 2 weeks of events.
I hope this helps someone!
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.
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?
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
Many thanks for this solution, it has been taking up a lot of my time.
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]
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.
Trying to provide a current events view that displays everything happening in the next 24 hours (i.e. not the next 2 days. I have created a dummy "BEGIN" field as indicated above but cannot figure out how to filter it by the hour rather than by the day. Suggestions?
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.
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…
Awesome! This has saved my big time :)
When tring to use [Today] in the filter, getting error massage as " Filter value is not a text string". Can anyone help ?
Pingback:Quick tips: Managing closed web parts and calendar filtering | SharePoint Use Cases
Same problem .. trying to use Calendar Start Time in a formula
Calculated Start time always displays the date selected from the Calendar. But the calculated field always shows the actual start time of the recurring event.. (Formula is ‘= [Start Time]’ )
Any suggestions ?
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.
Thanks a lot!
Very good idea. This is exactly what I was looking for. Thanks for your work.
Have been reading all of the comments and I have a question regarding just the display of the Start Time field. I would like to create a view from a calendar that shows each recurring event item and it’s date – not the date/time which is what displays for the [Start Time] field.
Any suggestions?? Thanks!
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!
@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.
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.
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.
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. :(
Pingback:Creating simple tasks calendar for SharePoint sidebar | Gunnar Peipman - Programming Blog
Awesome! This worked perfectly! Thank you! I don’t see why in the world SharePoint would not allow filtering on the date field… weird!
Thanks Gunnar! Have same issue in SP 2013 On-Premise and your work-around did the trick. You Rock!
Best Regards,
Marc
Thanks Gunnar, this is useful for me! Do you have any experience working with Virto Sharepoint Calendars? http://www.virtosoftware.com/sharepoint/calendar-web-part I am thinking about switching to it
Hi,
Could you please help me. i am having a problem using filter by time. let me explain to my scenario.
I have a list of prayers. and there is five items. i wanted to show each item on mentioned time. How can i do that?
Like: Prayers1 1:30 PM
Prayers2 4:30 PM
Prayers3 7:30 PM
How can i use the filter between time?
11 years later… thank you for this post!
13 years later, still no fix. MS products are full of these quirks that have been around for years, instead of fixing them they keep creating new products to drive revenue. Makes me sick.