Help with Excluding Holidays in my formula

John Ciarrochi

Hello SS Community, I've been trying to figure out how to exclude Weekends and Holidays in my formula that showcases the DIF between two dates. I got the weekends figured out along with implementing the IFERROR so the "Invalid Data Entry" no longer appears in the Cell

=IFERROR(NETWORKDAY([Date Samples Ready or Shipped]@row, [Date Analysis Completed]@row), "")

However, I can't seem to nail down the exclusion of Holidays. I have a Column named Holidays and a range of each Holiday the company honors - Range is [Holidays]1:[Holidays]19 but it appears no matter how I set this up I get UNPARSEABLE error. I've tried it a couple of ways based on what I've researched, see below:

=IFERROR(NETWORKDAY([Date Samples Ready or Shipped]@row, [Date Analysis Completed]@row), [Holidays ]1:[Holidays ]19, ""))

Also

=IFERROR(NETWORKDAY([Date Samples Ready or Shipped]@row, [Date Analysis Completed]@row), ""), [Holidays ]1:[Holidays ]19))

Also tried with only one parenthesis at teh end

Neither one working. Any suggestions you can provide would be greatly appreciated.

Thank you!

John Ciarrochi

Jeff Reisman

@John Ciarrochi I see a few issues here.

First - hold off on IFERROR until you are certain the underlying formula works. When you do use IFERROR, you need it to completely encompass the formula that it's testing for errors.

Second, it looks like there's an extra space after the Holidays column name. And for one-word column names that don't contain numbers or other characters, you don't even need the square brackets. (It won't hurt, they're just unnecessary.)

Third, make sure your Holidays column is date-type column and that the values in it are properly formatted as dates. (Click on a cell with a date in it - if there's a blue calendar icon on the right side of the cell, you're good.)

Try this first:

=NETWORKDAY([Date Samples Ready or Shipped]@row, [Date Analysis Completed]@row, Holidays1:Holidays19)

If that works, add your IFERROR around the formula:

=IFERROR(NETWORKDAY([Date Samples Ready or Shipped]@row, [Date Analysis Completed]@row, Holidays1:Holidays19), "")

Always check the parentheses color coding to make sure the formula elements are all contained as they should be. In this case, the "(" after IFERROR should be the same color as the very last ")", and the "(" after NETWORKDAY should be the same color as the ")" after Holidays19.

John Ciarrochi

Hello Jeff,

Thanks for the reply back and all the guidance. That appears to work, I had the Holidays Column set to Date Data type and tried your suggested formula and it didn't work, so I changed the column properties check box to Restrict to dates only and it worked - Strange. However, the associated Holidays are defined in Rows 1 thru 19 and as I scroll down, I noticed the Holidays Row changes to reflect what row your currently on - Is there any way to have the formula just look for the Holiday dates that are defined in that particular Range (Rows 1 thru 19)? see example below - there is no Date in the Holiday Column for row 27 thru 45, only 1 thru 19.

=IFERROR(NETWORKDAY([Date Samples Ready or Shipped]@row, [Date Analysis Completed]@row, Holidays27:Holidays45), "")

Thanks for all your help.

John C.

Jeff Reisman

@John Ciarrochi There are two ways to fix that issue:

If you only have those holiday dates in the Holidays column, and nothing else, just take the row numbers off:

=IFERROR(NETWORKDAY([Date Samples Ready or Shipped]@row, [Date Analysis Completed]@row, Holidays:Holidays), "")

If there are other dates in that column that you don't want to exclude as Holidays, you can use a $ between the column name and row number. This tells Smartsheet that this is an absolute value that should not be changed as you copy the formula down:

=IFERROR(NETWORKDAY([Date Samples Ready or Shipped]@row, [Date Analysis Completed]@row, Holidays$1:Holidays$19), "")

Jeff Reisman

@John Ciarrochi I see a few issues here.

First - hold off on IFERROR until you are certain the underlying formula works. When you do use IFERROR, you need it to completely encompass the formula that it's testing for errors.

Second, it looks like there's an extra space after the Holidays column name. And for one-word column names that don't contain numbers or other characters, you don't even need the square brackets. (It won't hurt, they're just unnecessary.)

Third, make sure your Holidays column is date-type column and that the values in it are properly formatted as dates. (Click on a cell with a date in it - if there's a blue calendar icon on the right side of the cell, you're good.)

Try this first:

=NETWORKDAY([Date Samples Ready or Shipped]@row, [Date Analysis Completed]@row, Holidays1:Holidays19)

If that works, add your IFERROR around the formula:

=IFERROR(NETWORKDAY([Date Samples Ready or Shipped]@row, [Date Analysis Completed]@row, Holidays1:Holidays19), "")

Always check the parentheses color coding to make sure the formula elements are all contained as they should be. In this case, the "(" after IFERROR should be the same color as the very last ")", and the "(" after NETWORKDAY should be the same color as the ")" after Holidays19.

John Ciarrochi

Hello Jeff,

Thanks for the reply back and all the guidance. That appears to work, I had the Holidays Column set to Date Data type and tried your suggested formula and it didn't work, so I changed the column properties check box to Restrict to dates only and it worked - Strange. However, the associated Holidays are defined in Rows 1 thru 19 and as I scroll down, I noticed the Holidays Row changes to reflect what row your currently on - Is there any way to have the formula just look for the Holiday dates that are defined in that particular Range (Rows 1 thru 19)? see example below - there is no Date in the Holiday Column for row 27 thru 45, only 1 thru 19.

=IFERROR(NETWORKDAY([Date Samples Ready or Shipped]@row, [Date Analysis Completed]@row, Holidays27:Holidays45), "")

Thanks for all your help.

John C.

Jeff Reisman

@John Ciarrochi There are two ways to fix that issue:

If you only have those holiday dates in the Holidays column, and nothing else, just take the row numbers off:

=IFERROR(NETWORKDAY([Date Samples Ready or Shipped]@row, [Date Analysis Completed]@row, Holidays:Holidays), "")

If there are other dates in that column that you don't want to exclude as Holidays, you can use a $ between the column name and row number. This tells Smartsheet that this is an absolute value that should not be changed as you copy the formula down:

=IFERROR(NETWORKDAY([Date Samples Ready or Shipped]@row, [Date Analysis Completed]@row, Holidays$1:Holidays$19), "")

John Ciarrochi

Jeff,

That did the trick!

Thank you for all your help - very insightful.

John Ciarrochi