Making Negative Numbers Display as Zero

Options
Linda Armstrong
edited 03/08/23 in Formulas and Functions

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


Tags:

Best Answer

Answers

  • Paul H
    Paul H ✭✭✭✭✭✭
    Options

    Try this

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

  • Linda Armstrong
    edited 03/08/23
    Options

    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
    Options

    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
    Paul H ✭✭✭✭✭✭
    Answer ✓
    Options

    @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
    Options

    @Paul H

    Thank you! This worked perfectly.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!