Formula Help - Predecessors disabled/Duration calculated formula to exclude holidays

Lori Khoury

Hello Smartsheet Community

I had to disable predecessors in this project plan. I worked with the ProDesk to come up with a way to have a calculated weighted formula. There is also a formula that calculate the # of days between start date and end date. However, I just realized that this formula does not excludes holidays. Is there anything I can add the first formula below to exclude holidays and only count for working days?

=IFERROR(NETWORKDAYS([Start Date]@row, [End Date]@row), " ")

=IFERROR(IF([Percent Complete Helper]@row = 1, (AVGW(CHILDREN(), CHILDREN(Duration@row))), [Enter % Complete]@row), 0)

Lori Khoury

**

Paul Newcome

I would suggest creating a table where each of the holiday dates are entered down a single column. You can then reference this list in your NETWORKDAYS function.

=NETWORKDAYS(start_date, end_date, {holidays})

Lori Khoury

Hi @Paul Newcome that is a great idea. I went ahead and tried it out. I added a holidays column and added your formula. =NETWORKDAYS([Start Date]@row, [End Date]@row, Holidays4:Holidays751) It worked but I had a couple of questions. Is there any way I can convert this to a column formula so that I can continue to lock the column? Also, how would I include the " " from the formula that I have in place. I believe the " " basically tell the formula to just leave it blank

=IFERROR(NETWORKDAYS([Start Date]@row, [End Date]@row), " ")

Paul Newcome

Just refer to the entire Holidays column

Holidays:Holidays

To leave it blank if there is an error, you would wrap the whole NETWORKDAYS function in the IFERROR the same way you did before.