Networkday counting excluding holidays
I am counting 2 dates in two separate columns and keep getting errors when I add the holiday syntax. What is the correct syntax.
Best Answers
-
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
-
Randy, Enabling dependencies turns some columns into formulas. You didn't do anything wrong. Article on what it does is below.
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
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
-
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
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
-
Randy, Enabling dependencies turns some columns into formulas. You didn't do anything wrong. Article on what it does is below.
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
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Mark,
Thanks for your help. It worked.
-
Happy to help, Randy. Thank you for contributing to the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!