Using Office 365 query list action with date range filters

Not applicable
4 2 4,156

Recently, I was asked to query a list for values based on date range entered by end users while developing a Nintex Workflow in Office 365.  For example, what if the workflow needs to determine the amount of stat holidays that falls within the range a user entered.  This seems simple enough to implement using the Office 365 Query List action.  You drag the action onto the workflow designer and you configure it with the target list, the fields to return, the credentials to use and of course the filter.  [statHolidayDate] is greater than or equal to the [StartDate] and [statHolidayDate] is less than or equal to the [EndDate].  The screen shot below shows the filter configuration:

This configuration will return zero results in Office 365. But why?  To answer that question I decided to remove the filter and then log the statHolidayDate value and as well as the start and end dates the user entered to the workflow history and found the answer staring back at me. See below:

The date format that was returned from the O365 query list was "yyyy-MM-dd h:mm AM/PM" and the date format entered by the user in the
current list item date/time field was "M/dd/yyyy h:mm AM/PM".  I then confirmed the date format in the Stat Holiday list (my lookup list) and found the format to be the same format as that stored in the current list item entered by which is the date/time format of Office 365 "M/dd/yyyy h:mm AM/PM" and we have little/no control over that format.  So, why does the O365 query list return a date format that is different from the format used in Office 365? And, how do I solve this issue?

To answer the first question, I asked this question on the Nintex forum here.

For the second question I did the following:

  • I created two new calculated (calculated based on other columns) Date time fields. The first one is StartDateCalc and the other EndDateCalc.
  • In the formula for these two fields I took the value of the original “StartDate” or “EndDate” depending on the field that is being configured. For example, the formula for StartDateCalc is =TEXT(StartDate,"yyyy-MM-dd h:mm AM/PM") and the formula for EndDateCalc is =TEXT(EndDate,"yyyy-MM-dd h:mm AM/PM"). See example below:

  • Note there is no need to show the calculated fields in any of the list views or on the custom Nintex input form but I included it in a view to validate the results. below is an example:

  • Then I configured the filter in the O365 query list action as before but this time I use the calculated fields in the filter criteria. For example: [statHolidayDate] is greater than or equal to the [StartDateCalc] and [statHolidayDate] is less than or equal to the [EndDateCalc].

This is how I implemented a solution to this issue.  Let know if this solution is helpful or if you were faced with a similar issue and how you solved it.

Nintex Newbie

Just to complete what Ozell said

In my case, [statHolidayDate] is a date variable. I need to convert it to a text variable with a "set workflow variable" action and select return type as ISO format.

In Office 365 environment, CAML Query helper SharePoint CAML Query Helper for 2007, 2010, 2013, Office 365 SharePoint Online - Home is great to test before designing workflow!

Nintex Newbie

Thanks a lot for this advise, it was the missing link.


In the end I found out, that I don't even need the computed column and the "Set workflow variable" action in my szenario. I compute a Time/Date Nintex variable  "ComparisonDate" (Now-30 days) in a "Add time to date"-Action. I want to query a list and get all the entries, which are older then the "ComparisonDate". This is possible with the "Office 365 query list"-Action if you pass the "ComparisonDate" in ISO format for the filtering/comparing. So there is no need to use a "Set workflow variable" action, if it was declared before.