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)

7 comments:

  1. Hi Zeev,
    Very close! Thanks for putting that up. I've incoporated it into my if statement (with the -1 at the end for work conducted on the first day) and tested in Excel.

    The only error occurs if the install finishes on a Friday. In this case your formula incorrectly adds on the last weekend and pushes the Install Finish Date to the Sunday of that weekend (the Monday if my -1 wasn't there at the end). Any idea how to fix?

    Thanks
    TC

    ReplyDelete
  2. Surely the -1 will affect the formula. I suggest to try replacing [Days_To_Add] with ([Days_To_Add] -1) across the board. Please let me know if this worked.

    ReplyDelete
  3. Yes worked perfectly, thank you so much!
    TC

    ReplyDelete
  4. This comment has been removed by a blog administrator.

    ReplyDelete
  5. I am looking for a formula to add 20 days if condition on another column is x and 40 days if condition is y excluding weekends

    ReplyDelete
    Replies
    1. If you never start the count on weekends than 20 work days are always 4 full weeks or 28 calendar days. 40 are 56.
      Therefore you can apply this formula:

      [Start_Date]+IF([Cond_Field]=x,28, IF([Cond_Field=y],56,0))

      If you need to adjust for starting on weekend you need to apply the conditions to the formula explained at the top.

      Delete

Enter your comment here