How to fix adding 1 or -1 to date difference calculations

Ethan T

Hello,

I'm trying to set up a formula which will calculate the difference between two dates to achieve a days overdue number for our orders. Current formula is:

=IF(Status@row = "Delivered", NETWORKDAYS([Delivery Date Expected]@row, [Delivered Date]@row))

This is returning an almost correct result, except it always adds a day since Smartsheet considers the same day to be a duration of 1 day. For example, if something was due on Jan 1 and was delivered on Jan 1, Smartsheet determines this to be 1 day overdue, which it isn't. It's 0 days overdue. The obvious solution here would be to subtract 1 from the result, which I tried, but then in cases where the order is completed ahead of schedule, it adds -1 to an already negative count, therefore making it -2 of the actual value. Here's an example:

As you can see, items that were delivered on time are marked as 1 day overdue, and items that are delivered early are marked as -1 of what they should be. The line where the item was delivered March 7th but expected March 10th should be -1, since it crossed over a weekend, but it shows -2. If I apply -1 to the formula to correct the positive values, it skews the negative values. Anyone have any ideas as to how I might correct this, or if there's a way to get Smartsheet to only start counting a difference in dates if the dates are actually different?

Any advice would be greatly appreciated!

-Ethan

Soumitra Bhowmick

Try this

=NETWORKDAYS([Expected Delivery Date]@row, [Actual Delivery Date]@row) + IF([Expected Delivery Date]@row > [Actual Delivery Date]@row, 1, -1)

Soumitra Bhowmick

Try this

=NETWORKDAYS([Expected Delivery Date]@row, [Actual Delivery Date]@row) + IF([Expected Delivery Date]@row > [Actual Delivery Date]@row, 1, -1)

Ethan T

That worked! Thank you so much!

Soumitra Bhowmick

Glad to help 🙂