NETWORKDAYS - extra day calc adjustment
Hi all!
I am trying to reflect the actual number of working days between two dates. The NETWORKDAYS function includes the start and end dates in the formula, which adds an extra day, regardless of whether or not any of these dates are working/non-working days. For instance – this formula returns 1 day for the number of days between two exact working dates. (I think it should be 0). To avoid a drawn-out philosophical debate 😊, for my purposes, I want to remove this additional day by doing the following:
Adjust the number of days returned by the NETWORKDAYS function by subtracting a day from the formula (include “-1” at the end of the formula). However, if the second date occurs before the first, this number would be negative so including “-1” would show 2 extra days between these dates. In the example below, it used the formula: =(NETWORKDAYS([Forecast 12-Feb-24 - End]@row, [Planned End]@row)) - 1 in the last column. The first row inaccurately added 2 extra days while the the 2nd row is correcly adjusted. Any suggestions to adjust the formula to first determine if this number is negative, it would include “+1” at the end of the NETWORKDAYS entry?
I tried the following unsuccessfully: =IF([Planned End]@row < [Forecast 12-Feb-24 - End]@row), (NETWORKDAYS([Forecast 12-Feb-24 - End]@row, [Planned End]@row) +1), (NETWORKDAYS([Forecast 12-Feb-24 - End]@row, [Planned End]@row) -1)
Best Answer
-
One of the reason for the Unparseable error is the field name has Date in it. It should be Planned End.
The formula below will the result shown. Change field names to what you need.
=IFERROR(IF([End Date]@row = [Target End Date]@row, 0, IF([End Date]@row > [Target End Date]@row, NETWORKDAYS([Target End Date]@row, [End Date]@row) - 1, IF([End Date]@row < [Target End Date]@row, NETWORKDAYS([Target End Date]@row, [End Date]@row) + 1, ""))), "")
Answers
-
Try this.
=IFERROR(IF([Planned End Date]@row = [Forecast 12-Feb-24 - End]@row, 0, IF([Planned End Date]@row > [Forecast 12-Feb-24 - End]@row, NETWORKDAYS([Planned End Date]@row, [Forecast 12-Feb-24 - End]@row) - 1, IF([Forecast 12-Feb-24 - End]@row < [Planned End Date]@row, NETWORKDAYS([Planned End Date]@row, [Forecast 12-Feb-24 - End]@row) + 1, ""))), "")
If I didn't get the right order, refer to below and change End Date and Target End Date to correct fields.
=IFERROR(IF([End Date]@row = [Target End Date]@row, 0, IF([End Date]@row > [Target End Date]@row, NETWORKDAYS([Target End Date]@row, [End Date]@row) - 1, IF([End Date]@row < [Target End Date]@row, NETWORKDAYS([Target End Date]@row, [End Date]@row) + 1, ""))), "")
-
Thanks @dojones. I follow your logic in the formula and you did capture the date order correctly. However, I get "Unparseable" error :(…will look at it again closely
-
One of the reason for the Unparseable error is the field name has Date in it. It should be Planned End.
The formula below will the result shown. Change field names to what you need.
=IFERROR(IF([End Date]@row = [Target End Date]@row, 0, IF([End Date]@row > [Target End Date]@row, NETWORKDAYS([Target End Date]@row, [End Date]@row) - 1, IF([End Date]@row < [Target End Date]@row, NETWORKDAYS([Target End Date]@row, [End Date]@row) + 1, ""))), "")
-
That worked perfectly. Thank you @dojones!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 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
- 303 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
