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

Michael Cavanaugh

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

Paul Newcome

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))

Mark 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

Michael Cavanaugh

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 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

Michael Cavanaugh

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

Paul Newcome

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))

Michael Cavanaugh

Thanks Paul (and Mark),

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

Mark Cronk

Once again @Paul Newcome saves me! My hero.

Mark

Paul 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. 🤣