# Calculating Variance between Due Date & Date Delivered

✭✭

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

• ✭✭

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

• ✭✭✭✭✭
edited 10/19/22

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.

• ✭✭
edited 10/20/22

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

• ✭✭✭✭✭✭

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.

• ✭✭

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)

• ✭✭✭✭✭✭

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭

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

• ✭✭✭✭✭

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

• ✭✭

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

• ✭✭✭✭✭
edited 10/21/22

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)

• ✭✭

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!