Need formula help.

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

Find more posts tagged with

Formulas

Accepted answers

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

All comments

Oof! Thank you!!

Much appreciated!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.