Fun with Calculated Columns


On a recent project, we were working on a solution using just out-of-the-box capabilities.  The accounting team of our customer wanted to track tasks involved with the monthly close, and have due dates auto-populate based on the month-end close date.  We utilized calculated columns and a list template to come up with a solution they could easily maintain.

 The picture below is simplified version of the task list.

TaskList

The Task Due Date and Review Due Date are calculated columns that are populated by the Close Date column.  To associate the close date with every task, the accounting team puts the list into edit mode, enters the close date for the first task, and then drags the date down to the last task.  The task due date and review due date automatically populate.  This provided a quick and easy way for the accounting team to adjust their close without having to contact IT for support.  They could add, remove tasks, etc.  We made the list a list template, so the accounting group just creates a new list from the template every month.

List columns needed to make this work

A few additional columns were needed to make this work.  First, we needed to create a couple of columns (pictured below) to store the number of days the task and review are due in relation to the close date.

CalcDays

For instance, this picture shows the Payout Report task is due 17 days before the close date, and the review is due 15 days before the close date.  We use these numbers to calculate the task due date and review due date.  The calculation formulas for each column are:

  • Review Due Date: =IF(WEEKDAY([Close Date]-ReviewCalcDays)=1,[Close Date]-ReviewCalcDays+1,IF(WEEKDAY([Close Date]-ReviewCalcDays)=7,[Close Date]-ReviewCalcDays+2,[Close Date]-ReviewCalcDays))
  • Task Due Date: =IF(WEEKDAY([Close Date]-DueDateCalcDays)=1,[Close Date]-DueDateCalcDays+1,IF(WEEKDAY([Close Date]-DueDateCalcDays)=7,[Close Date]-DueDateCalcDays+2,[Close Date]-DueDateCalcDays))

The formulas ensure the dates returned do not fall on a weekend.  Calculated columns enabled us to create a solution that provided the accounting team with flexibility – they could modify the solution themselves to meet their needs – even though no one had experience with SharePoint before.  We could have created a more elaborate solution, including using a workflow to automatically provision the list, but that would have taken flexibility away from the accounting team because they could not maintain the workflow.  With this solution, they spend about 5 minutes a month creating a new task list from a list template, enter the close date for one task and copy the date down the list in edit mode, and they are off and running.

 

 

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