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:


  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