Networkday counting excluding holidays

Randy Jacques

I am counting 2 dates in two separate columns and keep getting errors when I add the holiday syntax. What is the correct syntax.

Randy Jacques

I am in a gantt view and tried to add holidays to the project settings. I clicked on dependencies and now every day on the sheet changed. What did I do wrong? Also the Holiday formula didn't work. Not sure what I am missing here

Mark Cronk

Randy, Enabling dependencies turns some columns into formulas. You didn't do anything wrong. Article on what it does is below.

https://help.smartsheet.com/articles/765727-enabling-dependencies-using-predecessors

Can you include a screenshot of the formula you're trying to use? Is it producing an error or just the wrong answer? Happy to keep trying to help you.

Mark

Mark Cronk

Hi @Randy Jacques ,

To exclude holidays in a networkdays formula the holiday dates need to be in a single range of cells. I don't think you can list multiple cells separately.

The syntax is =NETWORKDAY( start_date, end_date, [ holidays ]) where [holidays] is a single cell or a range or cells.

Help?

Mark

Randy Jacques

Mark,

Just to be clear, are you saying I need to have a column labeled Holidays with the dates I want excluded in each cell below

Mark Cronk

Hi Randy,

I find it easiest in these situations to create a new sheet "Holidays" or Non-work days" that contains the dates you want excluded. That way you have 1 place to maintain. And 1 source of data for others in your company with the same issue to use. The sheet only needs 1 column with the date. I would add a second column with the holiday name.

Then, in your =NETWORKDAY( start_date, end_date, [ holidays ]) formula do an external sheet reference to the date colum on your Holidays sheet.

I didn't ask if you've set you sheet up as a project plan. If you did, under plan properties there's place to enter other non-working days. That eliminates the need for the lookup.

Help?

Mark

Randy Jacques

I am in a gantt view and tried to add holidays to the project settings. I clicked on dependencies and now every day on the sheet changed. What did I do wrong? Also the Holiday formula didn't work. Not sure what I am missing here

Mark Cronk

Randy, Enabling dependencies turns some columns into formulas. You didn't do anything wrong. Article on what it does is below.

https://help.smartsheet.com/articles/765727-enabling-dependencies-using-predecessors

Can you include a screenshot of the formula you're trying to use? Is it producing an error or just the wrong answer? Happy to keep trying to help you.

Mark

Randy Jacques

Mark,

Thanks for your help. It worked.

Mark Cronk

Happy to help, Randy. Thank you for contributing to the Community.

Mark

Shandra Goldfinger

https://community.smartsheet.com/discussion/comment/275626#Comment_275626

I am trying to set up this formula in one of my sheets. It is set up as a project with the holidays filled out, but the holiday days are still being counted as work days.