Monday, April 27, 2015

Excluding Weekend Days from Date Calculation in SharePoint Calculated Column.

This post is an extension to a previous post dealing with work days. SharePoint 2013 incudes the WORKDAYS function that calculates dates excluding weekends and even holidays, a nice enhancement because in SP 2010 it is quite cumbersome to exclude weekdays in a calculated column.

Here is a brief explanation to understand the formula below:
  • The default translation of the function WEEKDAY(Start_Date) is 1 for Sunday, 2 for Monday…. And 7 for Saturday. You can check in Excel and if it is not so in your settings, you will have to do some adjustments to the calculation below.
  • Let’s say we need to add 50 days. This means 10 weeks, 5 work days a week, or: TRUNC(Days_To_Add/5,0) = 10,
    So we have TRUNC(Days_To_Add/5,0) * 7   = 70 to get the # of days passed.
  • If we have 53 days instead, we need add the extra 3 days or: MOD(Days_To_Add,5) = 3.
  • However, if the extra days fall into the weekend, we need to add 1 or 2 days depending if we need to compensate for the entire weekend or only Sunday. And this depends of the WEEKDAY(Start_Date) and MOD(). If the total of both > 6 than we need to add 2 days for the weekend. But, for Sunday we need only one, so we take back 1 from the total. And, we need to take back 2 in the case it is Sat or Sun, together with MOD = 0. (We didn’t have to add the 2 in the first place, but it was easier to go this way.)


So the formula would be:
[Start_Date]+TRUNC([Days_To_Add]/5,0)*7+MOD([Days_To_Add],5)+IF(WEEKDAY([Start_Date])+MOD([Days_To_Add],5)>6,2,0)-IF(WEEKDAY([Start_Date])=7,1,0)-IF(AND(OR(WEEKDAY([Start_Date])=7,WEEKDAY([Start_Date])=1),MOD([Days_To_Add],5)=0),2,0)