Making Negative Numbers Display as Zero

I am trying to calculate the number of working days between the due date and date received on a contract deliverable. The formula works great when the contract deliverable is received on time or after the due date. The problem I am having is if the contractor submits the deliverable early (prior to its due date) it is giving me negative numbers. How do I change the formula to show “0” instead of negative numbers? See the chart below.

The current formula I am using is: =IFERROR(NETWORKDAYS([Deliverable Due Date]@row, [Deliverable Received Date]@row) - 1, "").

Find more posts tagged with

Formulas

Accepted answers

Paul H

@Linda Armstrong

Try this one

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

All comments

Paul H

Try this

=IFERROR(IF(NETWORKDAYS([Due Date]@row, [Date Received]@row) < 0, 0, NETWORKDAYS([Due Date]@row, [Date Received]@row)), "")

Linda Armstrong

Hello @Paul H,

Thank you so much. I used the formula you provided and it gets rid of the negative numbers but it changed my Due Date Variances (in the example above the numbers changed to 7, 172 and 0). In my original formula I added -1 to the end of the formula to subtract one day (we allow one day for processing/accepting the deliverable).

If I put the -1 into the formula you provided the numbers on my Smartsheet convert back to their original values but all of the "0" values changed over to "-1".

Linda Armstrong

Hello @Paul H

Thank you so much. I forgot to tag your name originally in my first reply and unsure if adding your name to the edited reply would flag you so I am posting my question again.

I used the formula you provided and it gets rid of the negative numbers but it changed my Due Date Variances (in the example above the numbers changed to 7, 172 and 0). In my original formula I added -1 to the end of the formula to subtract one day (we allow one day for processing/accepting the deliverable).

If I put the -1 into the formula you provided the numbers on my Smartsheet convert back to their original values but all of the "0" values changed over to "-1".

Paul H

@Linda Armstrong

Try this one

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

Linda Armstrong

@Paul H

Thank you! This worked perfectly.

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.