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.

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.

    48 thoughts on “Filtering SharePoint calendar by Start Time

    • October 30, 2007 at 5:25 am
      Permalink

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

    • May 29, 2008 at 1:35 pm
      Permalink

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

    • May 29, 2008 at 1:35 pm
      Permalink

      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.

    • June 1, 2008 at 3:55 pm
      Permalink

      Any idea how to overcome the issue with recurring event?

    • August 4, 2008 at 4:35 pm
      Permalink

      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 5, 2008 at 10:02 pm
      Permalink

      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 20, 2008 at 8:41 pm
      Permalink

      Yeah,,,what Jason said.

      Desperately seeking solution.  

      Shame on MS for this one!

    • August 29, 2008 at 7:03 pm
      Permalink

      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.

    • September 28, 2008 at 8:25 am
      Permalink

      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! :)

    • October 7, 2008 at 12:36 am
      Permalink

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

    • October 30, 2008 at 4:13 pm
      Permalink

      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.

    • November 3, 2008 at 2:03 pm
      Permalink

      Hi

      Does anyone have a solution yet for the recurrence issue?

    • November 21, 2008 at 11:46 am
      Permalink

      This doesn’t work when you expand recurring events.

      Yair

    • February 9, 2009 at 10:43 am
      Permalink

      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.

    • February 16, 2009 at 4:18 pm
      Permalink

      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 26, 2009 at 2:55 pm
      Permalink

      Still having the same recurrence issue

    • March 26, 2009 at 10:35 pm
      Permalink

      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 at 10:45 pm
      Permalink

      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.

    • April 7, 2009 at 9:12 pm
      Permalink

      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 15, 2009 at 3:20 am
      Permalink

      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.

    • May 14, 2009 at 2:47 am
      Permalink

      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?

    • June 12, 2009 at 4:55 am
      Permalink

      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

    • July 23, 2009 at 10:08 pm
      Permalink

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

    • August 6, 2009 at 10:13 pm
      Permalink

      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]

    • October 22, 2009 at 9:17 am
      Permalink

      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 24, 2009 at 5:01 am
      Permalink

      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?

    • December 8, 2009 at 8:28 pm
      Permalink

      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 18, 2009 at 6:29 pm
      Permalink

      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…

    • February 18, 2010 at 9:41 am
      Permalink

      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

    • April 27, 2010 at 11:14 pm
      Permalink

      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 ?

    • September 9, 2010 at 9:11 pm
      Permalink

      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.

    • January 11, 2011 at 2:08 pm
      Permalink

      Thanks a lot!

    • January 12, 2011 at 7:49 pm
      Permalink

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

    • July 28, 2011 at 7:47 pm
      Permalink

      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!

    • September 18, 2011 at 11:20 pm
      Permalink

      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!

    • October 26, 2011 at 6:16 pm
      Permalink

      @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.

    • May 10, 2012 at 2:31 pm
      Permalink

      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 16, 2012 at 7:13 pm
      Permalink

      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.

    • July 19, 2012 at 9:56 pm
      Permalink

      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

    • May 30, 2013 at 6:02 pm
      Permalink

      Awesome! This worked perfectly! Thank you! I don’t see why in the world SharePoint would not allow filtering on the date field… weird!

    • November 17, 2015 at 9:38 pm
      Permalink

      Thanks Gunnar! Have same issue in SP 2013 On-Premise and your work-around did the trick. You Rock!

      Best Regards,
      Marc

    • December 11, 2015 at 8:00 am
      Permalink

      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?

    • October 24, 2018 at 2:34 pm
      Permalink

      11 years later… thank you for this post!

    Leave a Reply

    Your email address will not be published. Required fields are marked *