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.
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
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)
-
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
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!