SharePoint List: What is the formula for calculating vacation days without holidays and weekends?

Question

I have a SharePoint list with 2 columns (From and To) both formatted as DATE.

In a third column, the difference is calculated in days from From till To, but minus weekends and holidays.

So far I've tried the following:

# which counts the days of the week, regardless of whether
# it is a weekend or a public holiday
= (WEEKDAY ([to] - [from])) + 1

# did not work because of incorrect syntax
= SUM (INT ((WEEKDAY ([From] - {2,3,4,5,6}) + [To] - [From] / 7))

However, only the days are generally extrapolated and displayed, but understandably no holidays and weekends are included.

I've tried several options, but I can not use Excel syntax here.

Answer 1

For SharePoint the formulas are a bit different (strange sometimes :) ). please take a look at this for Your case:

=IF(ISERROR(DATEDIF(From,To,"d")),"",(DATEDIF(From,To,"d"))+1-INT(DATEDIF(From,To,"d")/7)*2-IF((WEEKDAY(To)-WEEKDAY(From))0),1,0)-IF(AND(NOT(WEEKDAY(From)=7),WEEKDAY(To)=7),1,0))

I tested it with a quick example on some sample list, and from my point of view works quite fine :)

also check a different approach here - this is a quite interesting approach but at first its a bit hard to understand... at least for me it was :P. But having all the additional columns may give You more flexibility.

also on Stackexchange You may find the same solution as the one I posted... the post is older then this. Please double check if the formula is the same.. if not I would take the older one.. probably more tested then this ;)

... hope this info will be of any help :)