Getting NETWORKDAYS to Work with Due Date Comparisons
Hello everyone,
I am using Smartsheet to capture a bunch of data for vendor project tracking. I'm adding a few helper columns to compare some dates so I can evaluate performance and trends. However, I seem to be having trouble with the data for one of these helper columns so hoping to share what I've got to see how I've got this wrong.
Date Columns:
* Creation Date - When the project was created
* Due Date - If there is a due date (optional)
* Vendor Start Date - When the vendor plans to begin the project (usually same day or next business day)
* Vendor Scheduled Date - When the vendor expects to deliver the project
* Vendor Delivery Date - When the vendor delivers the project
* Actual Delivery Date - When the project is actually completed (as some go through a review step which is identified via another column called Project Type)
I've the following helper columns and the formulas for each:
* Days before Vendor - =IFERROR((NETWORKDAYS([Creation Date]@row, [Vendor Start Date]@row) - 1), "") - This seems to work as expected based on the data I've reviewed
* Days with Vendor - =IFERROR((NETWORKDAYS([LSP Start Date]@row, [LSP Delivery Date]@row) - 1), "") - This also seems to work as expected based on the data I've reviewed
* Days with Reviewer - =IF([Project Type]@row = "Review", IFERROR((NETWORKDAYS([LSP Delivery Date]@row, [Actual Delivery Date]@row) - 1), "")) - This also seems to work. Blank if Project Type is not Review or if one of the dates is empty. Data seems to be accurate.
* Due vs. Vendor Delivery - =IFERROR((NETWORKDAYS([Due Date]@row, [LSP Delivery Date]@row)) + 1, "") - This also seems to work. However, I did have to change the -1 to +1 in order for the data to look accurate.
* Due vs. Reviewer - =IF([Project Type]@row = "Review", IFERROR((NETWORKDAYS([Due Date]@row, [Actual Delivery Date]@row) + 1), "")) - This does NOT seem to work as expected; see notes below.
No matter how much I change the last formula with either a positive number towards the end, a zero or a negative number, it doesn't seem the resultant output matches the correct expected value. For example, one one row a Due Date of 10/20/22 against an Actual Delivery Date of 10/24/2022 shows a value of 2, which is expected. But the row above it shows a Due Date of 10/20/2022 with an Actual Delivery Date of 10/19/2022 with a result of -3 when the correct value should be -1. It seems like the formula works when the Actual Delivery Date is AFTER/on the Due Date but when the Actual Delivery Date is BEFORE the Due Date, it seems to be incorrect (perhaps ignoring the weekend dates).
Do I have my formula wrong?
Best Answer
-
Thanks for the suggestion, Michael! I made a slight tweak to the formula to ensure that an empty Due Date doesn't throw an error (as we want it to be blank), so this seems to be what is working for me:
=IF([Project Type]@row = "Review", IFERROR(IF(NETWORKDAYS([Due Date]@row, [Actual Delivery Date]@row) < 0, NETWORKDAYS([Due Date]@row, [Actual Delivery Date]@row) + 1, IF(NETWORKDAYS([Due Date]@row, [Actual Delivery Date]@row) > 0, NETWORKDAYS([Due Date]@row, [Actual Delivery Date]@row) - 1)), ""))
Much appreciated!!
Answers
-
What if you did something like this:
=IF([Project Type]@row = "Review", if(NETWORKDAYS([Due Date]@row, [Actual Delivery Date]@row) < 0, NETWORKDAYS([Due Date]@row, [Actual Delivery Date]@row) + 1,if(NETWORKDAYS([Due Date]@row, [Actual Delivery Date]@row) > 0, NETWORKDAYS([Due Date]@row, [Actual Delivery Date]@row) - 1)))
-
Basically just use your existing formula but include an if statement where if the result is negative, you add 1 and if it's positive you subtract 1.
-
Thanks for the suggestion, Michael! I made a slight tweak to the formula to ensure that an empty Due Date doesn't throw an error (as we want it to be blank), so this seems to be what is working for me:
=IF([Project Type]@row = "Review", IFERROR(IF(NETWORKDAYS([Due Date]@row, [Actual Delivery Date]@row) < 0, NETWORKDAYS([Due Date]@row, [Actual Delivery Date]@row) + 1, IF(NETWORKDAYS([Due Date]@row, [Actual Delivery Date]@row) > 0, NETWORKDAYS([Due Date]@row, [Actual Delivery Date]@row) - 1)), ""))
Much appreciated!!
-
Awesome.
Help Article Resources
Categories
Check out the Formula Handbook template!
