Rolling 2 Weeks Solution


This solution walks through the steps to build a rolling 2 weeks view of a project task list, where you can always see what active tasks either start or come up due within 2 weeks from today’s date.  To build the solution, you need to make use of calculated columns and a new list view.  The solution is something a normal business user can implement using just the web browser.

The graphic below shows what the goal of our solution is – show tasks that have either a start date or due date that fall between today and 2 weeks into the future.

Rolling2Weeks

It is a dynamic solution, because the items will appear or fall off based on today’s date (which changes every day).

The process I go through when I need to build a solution in SharePoint that makes use of calculated columns is to build the solution in Excel first.  I can do my testing very quickly in Excel using formulas.  After I have created a solution that works, I can then architect the solution in SharePoint – it provides a very iterative and quick way to work.

I built out the following list in Excel.

ExcelTest1

Tasks 2-8 are the tasks that should appear in the rolling 2 weeks view.  Here’s the logic in the spreadsheet:

  • 2 Weeks from Today Column: =Today+14.  This tells us the date that is 2 weeks into the future from today’s date.
  • Start Date w/in 2 Weeks of Today Column: =IF(AND(C5>=$B$1,C5<=E5),”Yes”,”No”).  This says if the task start date is greater than or equal to Today AND is less than or equal to 2 weeks from today, enter the text Yes, otherwise enter the next No.  We see that tasks 4-8 have a start date that is greater than today and less than or equal to 2 weeks from today (12/24-1/7).
  • End Date w/in 2 Weeks of Today Column: =IF(AND(D5>=$B$1,D5<=E5),”Yes”,”No”).   This says if the task end date is greater than or equal to Today AND is less than or equal to 2 weeks from today, enter the text Yes, otherwise enter the text No.  We see that tasks 2-5 have a due date that is greater than today and less than or equal to 2 weeks from today (12/24-1/7).
  • Within 2 Weeks Column: =IF(OR(F5=”Yes”,G5=”Yes”),”Yes”,”No”).  This says if the tasks either starts within 2 weeks of today or ends within 2 weeks of today, enter the text Yes, otherwise enter the text No.  We see that tasks 2-8 meet this criteria, meaning these are the tasks that should appear within our rolling 2 weeks view.

I could have used one column utilizing one long formula in Excel to come to the same results, but I prefer to break up my logic into steps as it is easier to audit.  After getting my logic correct, the next task becomes how to build it in SharePoint.

Building the Solution in SharePoint

I built the Task list in SharePoint 2013 using the task list template and copied each of my tasks from Excel into the list (put the list in Edit mode and copy and paste from Excel).  I could create a view for the “start date within 2 weeks of today” and “end date within 2 weeks of today” with no additional columns being needed.

  • Start date within 2 weeks of today view: Create a filter where Start Date is greater than or equal to [Today], and Start Date is less than or equal to [Today]+14.  Tasks 4-8 are shown, just like in my Excel spreadsheet.
  • End date within 2 weeks of today view: Create a filter where Due Date is greater than or equal to [Today], and Due Date is less than or equal to [Today]+14.  Tasks 2-5 are shown, just like in my Excel spreadsheet.

At this point everything is working great.  The problem becomes we can’t reproduce the within 2 weeks column from our spreadsheet because we can not use the value of Today in a calculated column, you can only use it within a view.  So we have to architect a different way to accomplish what we want.

I ran across this blog post which talked about creating a view to show items due in the next 7 days (based on pre-SharePoint 2007 limitations).  It provided the logic I needed to accomplish the rolling 2 weeks solution.  I created a new calculated column entitled, “FilterDue14Days” with the formula of [Due Date]-14.

FilterDue

With this column in place, I could create a new view to meet our goal.  The logic of the view is pictured below.

FinalView

SharePoint’s view logic works from top-down, so SharePoint looks to see if the item’s start date is less than or equal to [Today]+14 or the filterdue14days value is less than or equal to today’s date.  If the item meets either of those criteria, it passes the first test.  Then the item’s due date must also be greater than or equal to today’s date, and it must still be an active item.  If it meets all of these criteria, it will display in our rolling 2 weeks view.  The final result, tasks 2-8 show in our view, just like in our Excel spreadsheet.

2weekitems

I created another section in my Excel spreadsheet with the same logic as my SharePoint solution to audit the results as well, pictured below.

2weekitemsExcel

This is just one way to accomplish this goal.  But I wanted to build the solution with no other technologies needed, mainly SharePoint Designer.  Hope it helps.

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s