Count the number of cells in a column with a date derived from a workday based on today-1 & holidays

05/04/21
Accepted

=COUNTIFS([DATE FINISHED],=WORKDAY(TODAY,-1,[HOLIDAYS:HOLIDAYS]))

Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    There is an extra set of square brackets around the HOLIDY:HOLIDAY range which will cause the #UNPARSEABLE error (removed below).


    =COUNTIF([DATE FINISHED]:[Date Finished], @cell=WORKDAY(TODAY(),-1, HOLIDAYS:HOLIDAYS))

Answers

  • Mark CronkMark Cronk ✭✭✭✭✭

    Try:

    =COUNTIF([DATE FINISHED]:[Date Finished], @cell=WORKDAY(TODAY(),-1,[HOLIDAYS:HOLIDAYS]))

    Your sheet will need to contain a column [holidays] that contains the holidays you want excluded.

    Mark


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

  • Thanks Mark,

    As you can see I'm new to smartsheet. Unfortunately this hasn't worked. I'm still trying to work out when columns need to be bracketed and/or doubled, and when you add "@cell" or "@row"...well pretty much everything.


  • Mark CronkMark Cronk ✭✭✭✭✭

    Hi Michael,

    No worries. Happy to help you get started. Seeing your sheet is a huge help. Can you a screenshot of all the column names?

    Column names go in bracket [ ] when they are more than 1word or contain special characters.

    @row and @cell make your formulas more efficient. Best practice is to use @row instead of a row number if the formula is referring to cells in the same row. Using @row is required if you want to use column formulas - same formula automatically applied to the entire column in the sheet. @cell can be used with formulas that are evaluating an entire column to make the formula more efficient.

    Mark


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

  • This is a wide sheet...so I've split it under the full one. Hope you can read this?


  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    There is an extra set of square brackets around the HOLIDY:HOLIDAY range which will cause the #UNPARSEABLE error (removed below).


    =COUNTIF([DATE FINISHED]:[Date Finished], @cell=WORKDAY(TODAY(),-1, HOLIDAYS:HOLIDAYS))

  • Thanks Paul (and Mark),

    I'm sure I'll be back quite often.

  • Mark CronkMark Cronk ✭✭✭✭✭

    Once again @Paul Newcome saves me! My hero.

    Mark


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

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Michael Cavanaugh Happy to help. 👍️ I can say from experience that this Community has A TON of knowledge floating around. It is definitely a great place to search for answers.


    @Mark Cronk Haha. No worries. A misplaced parenthesis got me yesterday and someone else caught it. Even heroes make mistakes. 🤣

Sign In or Register to comment.