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?
