## 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?

Thanks
TC

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!
TC

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

5. Can't thank you enough

6. 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.

7. how to exclude holidays?