Calculating Variance between Due Date & Date Delivered

kelele20

I am new to smartsheet and I am looking for some guidance:

I want to calculate the delivery variance between the Due Date & Date Delivered. Both the columns are Column Types is Date

(I also tried with Text/Number, but doesn't help.)

The issue I have is that for any date that it is delivered on or after the due date, it is calculating an extra day. See below:

My Formula is:

=IF([Date Delivered]@row = "", "", NETWORKDAYS([Due Date]@row, [Date Delivered]@row))

Thank you

kelele20

My column properties are the same, however, it still shows invalid when there is no entry.

=Chris Palmer

Hello @kelele20,

Perhaps I'm oversimplifying, but if I'm understanding correctly one of these should work:

For Workdays:

=WORKDAY([Date Delivered]@row, 0) - WORKDAY([Due Date]@row, 0)

For Total Duration of Days:

=[Date Delivered]@row - [Due Date]@row

Let me know if either of these work for you.

kelele20

Neither of them works. The thought process is eg. If the Due date is 10/19, and if the Date Delivered is 10/19, then my variance is ), however, it shows as 1.

Basically, i need to combine these 2 formulas into 1:

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

&

=IF([Date Delivered]@row > [Due Date]@row, "", NETWORKDAYS([Due Date]@row, [Date Delivered]@row))

Thanks

Paul Newcome

If you combine the two then you will always have a blank. Your first says if the Date Delivered is less than or equal to and your second says if the Date Delivered is greater than.

kelele20

I was able to combine it:

=IF([Date Delivered]@row >= [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Delivered]@row) - 1, NETWORKDAYS([Due Date]@row, [Date Delivered]@row))

However, when the date delivered is blank its showing invalid. How can I tweak the above formula, so that when the date delivered is blank, then it doesn't show invalid. (but rather the cell remains blank)

Andrée Starå

Hi @kelele20

I hope you're well and safe!

Try something like this.

=IF([Date Delivered]@row<>"",IF([Date Delivered]@row >= [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Delivered]@row) - 1, NETWORKDAYS([Due Date]@row, [Date Delivered]@row)

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

kelele20

When i use that function:

=IF([Date Delivered]@row<>"",IF([Date Delivered]@row >= [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Delivered]@row) - 1, NETWORKDAYS([Due Date]@row, [Date Delivered]@row)

then the calculation for when the date delivered is earlier than the due date is inaccurate i.e if due date is 11/01, and delivered on 10/31 then its -1, however it shows as -2

I also tried this formula, but it didn't work:

=IF([Date Delivered]@row<>"",IF([Date Delivered]@row >= [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Delivered]@row), NETWORKDAYS([Due Date]@row, [Date Delivered]@row)-1

=Chris Palmer

I tested this out, and it's working for me.

kelele20

What are your columns properties? I replicated the same, but shows invalid, even after entering both dates.

=Chris Palmer

https://community.smartsheet.com/discussion/comment/348306#Comment_348306

Column Properties for Due Date is "Date"

Column Properties for Date Delivered is "Date"

Column Properties for Formula is "Text/Number"

=WORKDAY([Date Delivered]@row, 0) - WORKDAY([Due Date]@row, 0)

kelele20

My column properties are the same, however, it still shows invalid when there is no entry.