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)

8 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
  6. how to exclude holidays?

    ReplyDelete

Enter your comment here