NETWORKDAYS - extra day calc adjustment

selasa

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)

dojones

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, ""))), "")

dojones

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, ""))), "")

selasa

https://community.smartsheet.com/discussion/comment/434556#Comment_434556

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

dojones

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, ""))), "")

selasa

That worked perfectly. Thank you @dojones!!