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

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)

Find more posts tagged with

Formulas

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.