# Need formula help.

Options
✭✭✭

I have the following formula:

=IFERROR(IF([Date Complete]@row = [Due Date]@row, 0, IF([Date Complete]@row > [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Complete]@row) - 1, IF([Date Complete]@row < [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Complete]@row) + 1, ""))), "")

How do I add a contingency for if the Date Complete row is blank? I want Smartsheet to treat a blank Date Complete field as Current Date for purposes of Task Health.

Anne

Tags:

• ✭✭✭✭✭✭
Options

You can introduce a whole series of IFs before the one above to handle blank completed date. The portion in bold starts with a logical expression (If Date complete is blank) and specifies a series of nested IFs to consider if that is true, and if the Date Complete is not blank, start working these other nested IFs. (Double check the color coding on the parentheses once you put this in Smartsheet!)

=IFERROR(IF([Date Complete]@row = "", (IF(TODAY() = [Due Date]@row, 0, IF(TODAY() > [Due Date]@row, NETWORKDAYS([Due Date]@row, TODAY()) - 1, IF(TODAY() < [Due Date]@row, NETWORKDAYS([Due Date]@row, TODAY()) + 1, "")))), IF([Date Complete]@row = [Due Date]@row, 0, IF([Date Complete]@row > [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Complete]@row) - 1, IF([Date Complete]@row < [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Complete]@row) + 1, "")))), "")

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
Options

You can introduce a whole series of IFs before the one above to handle blank completed date. The portion in bold starts with a logical expression (If Date complete is blank) and specifies a series of nested IFs to consider if that is true, and if the Date Complete is not blank, start working these other nested IFs. (Double check the color coding on the parentheses once you put this in Smartsheet!)

=IFERROR(IF([Date Complete]@row = "", (IF(TODAY() = [Due Date]@row, 0, IF(TODAY() > [Due Date]@row, NETWORKDAYS([Due Date]@row, TODAY()) - 1, IF(TODAY() < [Due Date]@row, NETWORKDAYS([Due Date]@row, TODAY()) + 1, "")))), IF([Date Complete]@row = [Due Date]@row, 0, IF([Date Complete]@row > [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Complete]@row) - 1, IF([Date Complete]@row < [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Complete]@row) + 1, "")))), "")

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭
Options

Oof! Thank you!!

Much appreciated!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!