Exclusion of weekdays and holidays from a formula

Hello All,

I am currently trying to "decode" a pivot table that is calculating the average number of days that it takes to complete tasks. My objective is to adjust the formulas, so that all weekends and holidays will be excluded from calculations. I have been provided with a list of the holidays that the company follows during the year (there are 12 in total). Weekends are 52 a year, which leads to 104 days.

The column calculating durations it takes to complete each item looks like this:

If(OR(A2="",C2=""),"",C2-A2)

The A column contains dates (DD/MM/YYYY) for when the items were assigned to be built. The C column contains the dates (DD/MM/YYYY) for when the items were completed. Hence, if either of those columns contains a blank, the value of the timeframe calculation is blank. Otherwise, a positive or negative number appears, indicating the amount of days it took to complete the item. Negatives will appear for items that were assigned after they were completed.

I mention this column, because I believe it is the column containing the formula that I need to alter. Basically all that is needed, is for the formulas to take into account that holidays and weekends should be excluded.... E.G. if a time frame goes over a holiday or a weekend, it shouldn't count those days. So something that has, lets say, an assign date of today (6/19) and a completed date of 6/26, would calculate out to 5 instead of 7, since the weekend will not be counted.

I figure if the formula is changed in this column, then the pivot will spit out the adjusted data, since it will be taking an average of what will be a reduced number of days that the items are taking to be completed.

I understand the formula's for excluding holidays, as well as weekends. However, I am not sure how they can be applied within another formula, and if this is possible.

I would provide a spreadsheet, however it contains company-sensitive data. Any input is appreciated. I will try to work with any advice I receive here.

Thank you,

- J