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

Find more posts tagged with

Accepted answers

kelele20

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

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.