Filter a list based on two due date columns

Question

We have a list in SharePoint 2013, which we want to filter based on two date columns. The due dates refer to two different tasks.

We want to see only elements with due date within the next 7 days og either task 1 or task 2.

We filter like this:

[Me] and [DueDate1] is equal or less than [Today]+7 and [Duedate1] is equal or greater than [Today]+1

OR

[Me] and [DueDate2] is equal or less than [Today]+7 and [Duedate2] is equal or greater than [Today]+1

The result is 0 elements which is not correct.

Can't you make this advanced filtering method on two different date columns.

Best regards Morten

Answer 1

You're fighting the limitations of views not having grouping capability and calculated fields not allowing [today] to work.

We do this a lot - we have an item with up to 10 different due dates (an item is something that goes through a process with 10 sequential steps).

What we finally did was to create a wiki page and added the list multiple times per page, once per due date.

Maybe that would work for you? Add the list to the wiki page and change the filter to be on the first due date. Title the web part appropriately so they know which due date. Add the list again in a second web part, using the second due date filter.

For example:

Proofing Due Next Week
....item 1....
....item 2....
....item 3....

Final Approval Due Next Week
....item 3....
....item 5....

I know, not a coding answer, but thought I'd give you an alternative.

Another way to do it is through SharePoint Designer - but it isn't very maintainable. You can edit the view page in SPD (if you are on SharePoint 2013 you have to set the webpart of the view to server render) and change the filter to be the correct CAML statement you need. However, as soon as someone edits the view, you will lose that change, so it's not a recommended way to do it.

<AND>
     <OR>
        <AND>
           <Geq><FieldRef Name='DueDate1' /><Value type="DateTime'><Today OffsetDays='1'></Value></Geq>
           <Leq><FieldRef Name='DueDate1' /><Value type="DateTime'><Today OffsetDays='7'></Value><Leq>
        </AND>
        <AND>
           <Geq><FieldRef Name='DueDate2' /><Value type="DateTime'><Today OffsetDays='1'></Value></Geq>
           <Leq><FieldRef Name='DueDate2' /><Value type="DateTime'><Today OffsetDays='7'></Value><Leq>
       </AND>
     </OR>
     <Eq><FieldRef Name='AssignedTo' />Value Type='Integer'><UserID></Value></Eq>
</AND>