Need formula help.

Anne BWarren

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.

Thanks in advance.

Anne

Jeff Reisman

@Anne BWarren

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

Jeff Reisman

@Anne BWarren

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

Anne BWarren

@Jeff Reisman

Oof! Thank you!!

Much appreciated!