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

Try this one
=IFERROR(IF(NETWORKDAYS([Due Date]@row, [Date Received]@row)  1 < 0, 0, NETWORKDAYS([Due Date]@row, [Date Received]@row)  1), "")
Answers

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

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

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

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

Thank you! This worked perfectly.
Help Article Resources
Categories
Check out the Formula Handbook template!