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
-
My column properties are the same, however, it still shows invalid when there is no entry.
Answers
-
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.
https://www.linkedin.com/in/zchrispalmer/
-
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)
-
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
-
I tested this out, and it's working for me.
https://www.linkedin.com/in/zchrispalmer/
-
What are your columns properties? I replicated the same, but shows invalid, even after entering both dates.
-
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)
https://www.linkedin.com/in/zchrispalmer/
-
My column properties are the same, however, it still shows invalid when there is no entry.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!