NETWORKDAY function NOT giving me "0" when the two dates that I am comparing are the same.

Sean Corcoran

I am using the formula below and I am not getting the results that I would expect.

=IF([Date Completed]@row = "", "", NETWORKDAY([Original Due Date]@row, [Date Completed]@row))

example 1

[Original Due Date] 03/16/21 and [Date Completed} 03/16/21 I would expect to get back "0" because there is no difference in the dates but it gives me "1"

example 2

[Original Due Date] 08/16/21 and [Date Completed} 08/18/21 I would expect to get back "2" but it gives me "3"

example 3

[Original Due Date] 08/20/21 and [Date Completed} 08/18/21 I would expect to get back "-2" but it gives me "-3"

Is there another function I can use that would give me the results I want?

I know I could add at the end of the formula -1 but that only works if the result is a positive number. I would also have to add an "IF" statement to use +1 if the result is a negative number. If there is no other function to use I could use help with the "IF" statement part.

=IF([Date Completed]@row = "", "", NETWORKDAY([Original Due Date]@row, [Date Completed]@row)this is where I would need the "IF" statement)

Paul Newcome

The reason for this is because that particular function starts at the beginning of the first day and ends at the end of the last day. If you start and end a project on the same day, then it took one workday.

As for using the IF to denote minus or plus one, I would suggest this...

=IF([Date Completed]@row = "", "", NETWORKDAY([Original Due Date]@row, [Date Completed]@row + IF(NETWORKDAY([Original Due Date]@row, [Date Completed]@row) > 0, -1, 1))

Paul Newcome

Happy to help. 👍️

The NETWORKDAY formula is more geared towards duration as opposed to variance. For variance you're unfortunately stuck with the additional IF.

Paul Newcome

The reason for this is because that particular function starts at the beginning of the first day and ends at the end of the last day. If you start and end a project on the same day, then it took one workday.

As for using the IF to denote minus or plus one, I would suggest this...

=IF([Date Completed]@row = "", "", NETWORKDAY([Original Due Date]@row, [Date Completed]@row + IF(NETWORKDAY([Original Due Date]@row, [Date Completed]@row) > 0, -1, 1))

Sean Corcoran

@Paul Newcome

Thank you for the formula it worked great.

I understand your explanation of how the function works but in our case we want to report how many days over or under the task was to the due date but I really appreciate the formula

Paul Newcome

Happy to help. 👍️

The NETWORKDAY formula is more geared towards duration as opposed to variance. For variance you're unfortunately stuck with the additional IF.

YZ_123

Very helpful thank you.

I used the formula below and it is working well except for when both the estimated and the actual completion dates are missing (system interpreting it as estimated=actual=0).

How should I modify the formula so that it reflects an error message when both fields are blank?

(Note that I'm already using conditional formatting and rules to trigger the user to update the fields but the incorrect values of 0 are pulling in a dashboard and skewing the data)

=IFERROR(IF([Estimated Completion Date]@row = [Actual Completion Date]@row, "0", NETWORKDAY([Estimated Completion Date]@row, [Actual Completion Date]@row) + IF(NETWORKDAY([Estimated Completion Date]@row, [Actual Completion Date]@row) > 0, -1, 1)), "Missing Date")

Thank you.

Paul Newcome

@YZ_123 You would use an IF statement to say that if both are blank then output whatever it is you want to output then drop your existing formula in the 3rd portion of that IF.

=IF(AND([Estimated Date]@row = "", [Actual Date]@row = ""), "ERROR MESSAGE", IFERROR(..............................)), "Missing Date"))