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/nonworking 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 drawnout 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 12Feb24  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 12Feb24  End]@row), (NETWORKDAYS([Forecast 12Feb24  End]@row, [Planned End]@row) +1), (NETWORKDAYS([Forecast 12Feb24  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 12Feb24  End]@row, 0, IF([Planned End Date]@row > [Forecast 12Feb24  End]@row, NETWORKDAYS([Planned End Date]@row, [Forecast 12Feb24  End]@row)  1, IF([Forecast 12Feb24  End]@row < [Planned End Date]@row, NETWORKDAYS([Planned End Date]@row, [Forecast 12Feb24  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
Check out the Formula Handbook template!