NETWORKDAYS Concern With Future and Past Dates
Working on creating schedule variance icons, I am running across an issue which might be solved with absolute values of NETWORKDAYS but wanted to understand how to better structure the formula correctly. In this example I have two helper check columns Delta Check 1 and Delta Check 2 provided below. The problem I'm having is the +1 day being added to the differences in dates. I do not want to count the start date and to accurately reflect this need, I end up having to use two formulas instead of one column formula.
Screen shot above is using two different formulas, one for the past event and one for the future event for delta check 1. Using either the Past Event or the Future Event formulas shown below will incorrectly display date sums.
Past Event: =NETWORKDAYS(TODAY() - 1, [Start Date]@row)
Future Event: =NETWORKDAYS(TODAY(), [Start Date]@row) - 1
Control highlighted in blue was my original column formula: =NETWORKDAYS(TODAY(), [Start Date]@row)
For reference the TODAY() date is 2/26/21
Best Answers
-
Hi @Taylor Compton - NOAA Federal
You can add these two formulas together in a Nested IF statement to create one large formula that you can then make into a column formula.
In my formula example I have 4 IF statements.
1 . The first tells the formula what to do if the Start Date is Today's date:
=IF([Start Date]@row = TODAY(), 0,
2 . The second tells the formula what to do if the Start Date is yesterday. We need a separate rule for this because your "past event" statement minuses 1 from Today in the formula so it would say that yesterday is 1, when it should be -1.
IF([Start Date]@row = TODAY(-1), -1,
3 . The third statement is your "past event" formula. The IF statement check to see if the Start Date is less than Today, and then outputs your formula above.
IF([Start Date]@row < TODAY(), (NETWORKDAYS(TODAY() - 1, [Start Date]@row)),
4 . Otherwise, if the Start Date is in the future or greater than Today, we paste in your "future event" formula:
IF([Start Date]@row > TODAY(), (NETWORKDAYS(TODAY(), [Start Date]@row) - 1)))))
Try this Full Formula
=IF([Start Date]@row = TODAY(), 0, IF([Start Date]@row = TODAY(-1), -1, IF([Start Date]@row < TODAY(), (NETWORKDAYS(TODAY() - 1, [Start Date]@row)), IF([Start Date]@row > TODAY(), (NETWORKDAYS(TODAY(), [Start Date]@row) - 1)))))
Let me know if this works for you!
Cheers,
Genevieve
-
Hi @Taylor Compton - NOAA Federal
No problem, I'm glad that worked for you!
We can actually set up something really similar for your % complete. Let's say, if the Start Date is in the future, the Expected % Complete is 0. If the END DATE is in the Past, then Expected % Complete is 1 (or 100%). Otherwise, then we can use a similar formula to what you have above.
Try this:
=IF([Start Date]@row > TODAY(), 0, IF([End Date]@row <= TODAY(), 1, (NETWORKDAYS([Start Date]@row, TODAY()) / NETWORKDAYS([Start Date]@row, [End Date]@row))))
Broken Down:
Start Date in Future
=IF([Start Date]@row > TODAY(), 0,
End Date in Past
IF([End Date]@row <= TODAY(), 1,
Otherwise...work days between Start Date and Today divided by number of work days in the task.
(NETWORKDAYS([Start Date]@row, TODAY()) / NETWORKDAYS([Start Date]@row, [End Date]@row))))
Is this what you're looking for?
Cheers!
Genevieve
Answers
-
Hi @Taylor Compton - NOAA Federal
You can add these two formulas together in a Nested IF statement to create one large formula that you can then make into a column formula.
In my formula example I have 4 IF statements.
1 . The first tells the formula what to do if the Start Date is Today's date:
=IF([Start Date]@row = TODAY(), 0,
2 . The second tells the formula what to do if the Start Date is yesterday. We need a separate rule for this because your "past event" statement minuses 1 from Today in the formula so it would say that yesterday is 1, when it should be -1.
IF([Start Date]@row = TODAY(-1), -1,
3 . The third statement is your "past event" formula. The IF statement check to see if the Start Date is less than Today, and then outputs your formula above.
IF([Start Date]@row < TODAY(), (NETWORKDAYS(TODAY() - 1, [Start Date]@row)),
4 . Otherwise, if the Start Date is in the future or greater than Today, we paste in your "future event" formula:
IF([Start Date]@row > TODAY(), (NETWORKDAYS(TODAY(), [Start Date]@row) - 1)))))
Try this Full Formula
=IF([Start Date]@row = TODAY(), 0, IF([Start Date]@row = TODAY(-1), -1, IF([Start Date]@row < TODAY(), (NETWORKDAYS(TODAY() - 1, [Start Date]@row)), IF([Start Date]@row > TODAY(), (NETWORKDAYS(TODAY(), [Start Date]@row) - 1)))))
Let me know if this works for you!
Cheers,
Genevieve
-
Thank you for your considerate reply. I've tested the formula and it is working as expected. The use of nested formulas is a bit new to me and complex. I'll keep the logic you outlined in mind if I come across another multi-variant problem.
On a related note of NETWORKDAYS, my Expected % Complete calculation is incorrectly showing percentages for tasks in the future and therefore marking them as "Red" and off track. I'm able to have the calculation work if I do not use NETWORKDAYS and wanted to know if there is a preference to use one formula over the other.
Current Formula: =IFERROR((NETWORKDAYS(TODAY(), [Start Date]@row)) / (NETWORKDAYS([End Date]@row, [Start Date]@row)), 0)
Shows Expected % Complete with a start date and end date of 03/08/21 as 400% (should be zero based TODAY() being 03/03/21)
Old Formula: =IFERROR((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row), 0)
Shows Shows Expected % Complete with a start date and end date of 03/19/21 as 0% (which is correct, but would expect a negative value)
-
Hi @Taylor Compton - NOAA Federal
No problem, I'm glad that worked for you!
We can actually set up something really similar for your % complete. Let's say, if the Start Date is in the future, the Expected % Complete is 0. If the END DATE is in the Past, then Expected % Complete is 1 (or 100%). Otherwise, then we can use a similar formula to what you have above.
Try this:
=IF([Start Date]@row > TODAY(), 0, IF([End Date]@row <= TODAY(), 1, (NETWORKDAYS([Start Date]@row, TODAY()) / NETWORKDAYS([Start Date]@row, [End Date]@row))))
Broken Down:
Start Date in Future
=IF([Start Date]@row > TODAY(), 0,
End Date in Past
IF([End Date]@row <= TODAY(), 1,
Otherwise...work days between Start Date and Today divided by number of work days in the task.
(NETWORKDAYS([Start Date]@row, TODAY()) / NETWORKDAYS([Start Date]@row, [End Date]@row))))
Is this what you're looking for?
Cheers!
Genevieve
-
@Gabriella Silvagnoli this is perfect! Thank you for the explanation as well. This is exactly what we are wanting to display.
-
Happy to help! 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!
