Do Not Count Holidays and Less than Zero
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.
Comments
-
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
-
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))
Help Article Resources
Categories
Check out the Formula Handbook template!
