NETWORKDAYS and WORKDAY Discrepancy
Feel free to duplicate this yourself to see the problem:
I am using manually created durations in order to calculate the finish date and determine if a task could run late if it takes as long as forecasted.
The formula in Est. Duration is:
=NETWORKDAYS([Est. Start]@row, [Est. Finish]@row)
The formula in estDuration is:
=[Est. Duration]@row
The formula in calcFinish is:
=WORKDAY([Start Date]@row, VALUE(estDuration@row))
I need the helper for estDuration in order to convert Est. Duration to a number.
But if you look at the values, they are inconsistent. I understand that NETWORKDAYS includes the start date. In that case, "27" makes sense. But in going back the other way, with WORKDAY I should subtract 1.
However, look at the second row. It calculates the duration correctly AND it calculate the finish date correctly going back the other way.
Help!!!
Answers
-
The discrepancy is because you have non work days included in your start and end dates.
Row 1 start date is a Tuesday and is included in the networkdays between then and finish date of March 13, also a Tuesday. Adding networkdays to start date therefore gives you the finish date plus 1. As expected.
Row 2 start date is a Sunday, so does not get included in networkdays. Therefore there are only 3 working days between start and finish. 3 working days after Sunday is Monday 26, Tuesday 27, Wednesday 28. Adding networkdays to the start gives you the original finish date (no plus 1 as start date was not counted).
Row 3 start date is a Wednesday so is included in networkdays (like row 1). However, the finish day is a Saturday so not included. Adding the networkdays to the start date will therefore give you the original finish date as in row 2 (no plus 1, as the finishing Saturday is not included). However, as that is a Saturday the formula returns the next working day which is the Monday (11th).
Row 4 follows the same pattern as row 1.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!