Monday, April 27, 2015

Excluding Weekend Days from Date Calculation in SharePoint Calculated Column.


Here is a brief explanation to understand the formula below:

So the formula would be:


  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?


  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.

  3. Yes worked perfectly, thank you so much!

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

  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

    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.

  6. how to exclude holidays?


Enter your comment here