Networkday counting excluding holidays

Randy Jacques
Randy Jacques ✭✭✭
in Formulas and Functions

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

· Share on FacebookShare on Twitter

Best Answers

Answers

  • Mark Cronk
    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

    · Share on FacebookShare on Twitter
  • Randy Jacques
    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

    · Share on FacebookShare on Twitter
  • Mark Cronk
    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

    · Share on FacebookShare on Twitter
  • Randy Jacques
    Randy Jacques ✭✭✭
    Answer ✓

    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

    · Share on FacebookShare on Twitter
  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

    · Share on FacebookShare on Twitter
  • Randy Jacques
    Randy Jacques ✭✭✭

    Mark,


    Thanks for your help. It worked.

    · Share on FacebookShare on Twitter
  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    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.

    · Share on FacebookShare on Twitter
  • Shandra Goldfinger
    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.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!