Handling working days in SharePoint Calculated Columns

A client asked if an automated due date field could be setup to calculate a deadline for certain tasks in a custom SharePoint 2007 list.

My inspiration came from this blog: http://blog.pentalogic.net/2008/11/working-days-weekdays-holidays-sharepoint-calculated-columns/

After reading this article, I created a calculation column that works by reading the ‘request type’ selected when creating a new record and based on that choice, automatically sets the last date of response for the user. You will now see two ‘last response dates columns, an automatic one and your original column. Please let me know when or if you are happy for the original column to be removed.

For background information, this is the logic behind the column:

· If a ‘request type’ of ‘FOI’ is selected, the last date for response column sets the date to be 20 working days ahead of the date specified in the ‘date received’ column.

· If a ‘request type’ of ‘DP’ is selected, the last response column sets the date to be 40 calendar days ahead of the date specified in the ‘date received’ column.

· If a ‘request type’ of ‘EIR’ is selected, the last date for response columns sets the date to be 20 working days ahead of the date specified in the ‘date received’ column.

The calculation is as follows:

=IF([Request Type]="FOI",[Date received]+28,IF([Request Type]="DP",[Date received]+40,IF([Request Type]="EIR",[Date received]+28,[Date received]+28)))

Thinking about it, it became incredibly easy. If the due dates are always a multiple of 5 (e.g. 5,10,15,20) working days, in my case 20, then you will always have 2 weekend days in every block of five working days so you can just add 7,14,21,28 etc using =[Field]+28.

The only limitations with the calculations I’ve put into place are that they don’t take into account public holidays such as a bank holiday, christmas day etc. These are currently considered as a working / normal calendar day. Does anyone know, can this be done without creating an elaborate custom workflow?

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