Alana Helbig

How do I create a view of tasks that are due in this working week or in the next working week? For a quick fix we could use formulas such as [Today]+7 but this is just adding 7 days from today's date and therefore doesn't really give us what we are after. So give this a go instead….

Tip: SharePoint Calculated fields take the same formulas as those used in excel… I test my formulas in excel first before adding them into SharePoint. This can be much faster plus excel is a little nicer and will often tell you how to fix any errors in your formulas.

Show tasks due this week

Work out the week start date and the week end date for the week in which the due date falls.

• Create a calculated column in your task list called 'Week Start' and use the formula:

  =[Due Date]-(WEEKDAY([Due Date],2)-1)

• Create another calculated column in your task list called 'Week End' and use the formula:

  =[Due Date]+(7-WEEKDAY([Due Date],2))

• Your task list will look something like this… - check to make sure that your dates are correct

  

Create a new view and filter

• Create your new view and give it a name such as 'due this week'
• Filter the view as follows…..

  

   

Voila it's that easy! You now have a view that shows you all tasks due in the current working week!!

Read this blog if you would like to understand the weekday formula in more detail

Show tasks due next week

Once you have worked out the week start and week end dates in which the due date fall, it's very easy to work out tasks that are due next week, last week so on…..

Work out the previous week's start date and the previous week's end date of the due date

• Create a calculated column in your task list called 'Previous Week Start' and use the 'week start' column you created above to calculate by subtracting 7:

  =[Week Start]-7

• Create a calculated column in your task list called 'Previous Week End' and use the 'week start' column you created above to calculate by subtracting 1:

  =[Week Start]-1

• Your task list will look something like this… - check to make sure that your dates are correct

  

Create a new view and filter

• Create your new view and give it a name such as 'due next week'
• Filter the view as follows…..

  

You now have a view that shows you all tasks due in the next working week

Show tasks due this month

What about if we want to show tasks that are due this month and next month?

Work out the month start date and the month end date for the month in which the due date falls.

• Create a calculated column in your task list called 'Month Start' and use the formula:

  =DATE(YEAR([Due Date]),MONTH(Due Date),1)

• Create another calculated column in your task list called 'Month End' and use the formula:

  =DATE(YEAR([Due Date]),MONTH([Due Date])+1,1)-1

• Your task list will look something like this… - check to make sure that your dates are correct

  

   

Create a new view and filter

• Create your new view and give it a name such as 'due this month'
• Filter the view as follows…..

  

Show tasks due next month

Work out last month's start date and last month's end date of the due date

• Create a calculated column in your task list called 'Last Month Start' and use the formula:

  =DATE(YEAR([Due Date]),MONTH([Due Date])-1,1)

• Create another calculated column in your task list called 'Last Month End'
  • If you have created the 'month start' date column in the steps above you can use the formula:

    =[Month Start]-1

  • If you haven't followed the steps above, you can use the formula:

    =DATE(YEAR([Due Date]),MONTH([Due Date]),1)-1

• Your task list will look something like this… - check to make sure that your dates are correct

  

Create a new view and filter

• Create your new view and give it a name such as 'due next month'
• Filter the view as follows…..

  

Find out more about the date formula

You can also do this with calendars and other lists.....

One last thing – you might want to hide the calculated columns so that users don't see these fields when they are viewing existing task properties….

1. Open the settings page of the list and click on 'Advance Settings'

   

    

2. Allow management of content types and say ok

   

    

3. Back on the settings page of the list click on the 'Task' content type

   

    

4. Click on the first calculated column listed

   

    

5. Mark the column as hidden and say ok

   

    

6. Do this for the remaining columns