Do Not Count Holidays and Less than Zero

Just Jean

Could someone tell me how to write this Excel formula for Smartsheet?

Excel: exclude holidays and leave blank if date completed is empty.

=IF(NETWORKDAYS(E2,G2,Holiday)<0,"",NETWORKDAYS(E2,G2,Holiday))

 

Smartsheet: exclude holidays (I have a hidden holiday column) and leave work days total blank if date completed is empty.

=NETWORKDAYS([Date Assigned to Contractor]1, [Date Completed]1)

Thank you.

Shaine Greenwood

Hello Just Jean,

In Smartsheet, columns have unique names. When you want to reference an entire column, use <column name>:<column name> in your function. Example:

=IF(NETWORKDAYS([Date Assigned to Contractor]1, [Date Completed]1, Holiday:Holiday) < 0, "", NETWORKDAYS([Date Assigned to Contractor]1, [Date Completed]1, Holiday:Holiday))

Another approach for this is to define holidays in the sheet's Project Settings: https://help.smartsheet.com/articles/516392-defining-working-non-working-holidays-on-a-project-sheet

Just Jean

Thank you! If the Date Completed cell is blank, how do I keep that cell empty?

Is this correct?

=IF(ISDATE([Date Completed]3), NETWORKDAYS([Date Assigned to Contractor]3, [Date Completed]3, Holiday:Holiday))