Query calendar to determine if there is an event on a specific date

  • 2 September 2014
  • 5 replies
  • 5 views

Userlevel 3
Badge +9

I'm having an issue doing something I thought would be pretty simple.  We have a calendar in our company where our HR department maintains the list of corporate holidays.  I'm wanting to query the list for a given date, and determine if there is a holiday on the calendar.  The holiday events are setup as all day events, and if the even spans multiple days, then the start/end dates cover a span of days.

 

So in my workflow I have a single date that I'm attempting to query the calendar with, and determine if there is a holiday on that date.  Howeve the issue I'm having is that I'm getting results back for the days when there isn't an event.  For example there is an event for Thanksgiving that is starts on 11/27/2014 and ends on 11/28/2014.  However when I run a test using 11/26/2014, it returns the thankgsiving event in the results.  Here is how I have my Query List setup.  

 

Query_Holidays_List.png

 

I saw some posts on the old forum about changing the date to ISO format, so I have done that before I do the Query List.  But I still get the same results.  I'm stumped as to what the issue is.  Any assistance would be greatly appreciated.


5 replies

Userlevel 7
Badge +11

Hey Brendan Murphy‌ can you attach your workflow, and are you just using a standard SP Calendar no mods ?

Badge +3

Hi Brendan, I recall running into the same issue a while back. When using the all day event, the time is ignored (set to midnight by default). Your test runs and returns Thanksgiving because it matches your "End Time" condition. We got around this by setting the end time in our calendar event to 11:59 PM. I guess you could also try changing your End Time condition to "greater than" (not "greater than or equal to").

Userlevel 3
Badge +9

I thought the same thing, but in my input date I'm using different times, that don't match the times for the Thanksgving day event.  Plus if I do earlier days, those don't return a match.  I've tried changing the conditions to only "greater/less than" and that didn't work either.  When I output the start/end date for the Thanksgiving event, it shows it as:

Start Date: 11/27/2014 12:00:00 AM
End Date: 11/28/2014 11:59:00 PM

So I'm assuming this is the information the workflow is using for the comparision. 

Userlevel 3
Badge +9

Dan Stoll‌, I don't see a way to actually attach a file here, unless I'm missing it.  Below is a screen shot of the entire workflow.  It's pretty basic.  I have a date/time variable that displays on the start form where I'm inputing the date to check.  The first action is converting that date into an ISO date string and storing it in a single text variable (st_Date). 

Yes, it's just a standard calendar from what I can tell.  Our HR deptartment owns it, but I don't see anything that indicates it's been changed.  All the fields are the standard calendar fields.

Query_Holiday_List_Entire_WF.png

Badge +3

Were you able to get your WF to work?  I'm searching the forum for help.  I see a similarities with the issues I'm having.  I started to wonder if there are constraints on empty (blank or null) list items on date type columns, when looping through a list. 

Reply