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

Best Answer

Answers

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    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.

  • kelele20
    kelele20 ✭✭
    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

  • Paul Newcome
    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.

  • 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å
    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!

    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

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭

    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.

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    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)

  • kelele20
    kelele20 ✭✭
    Answer ✓

    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!