Question

Using this formula to calculate duration of a project

=NETWORKDAY([PoC start date]@row, [PoC End date]@row)

But for blank rows -  "Invalid Data Type" is entered

Hence when I try to average the duration - I am receiving "Invalid Data Type" error.

How can I edit the above Network days formula to ignore blank cells ?

Paul Newcome · Accepted Answer

Try an IFERROR like so:

=IFERROR(NETWORKDAY([PoC start date]@row, [PoC End date]@row), "")

Jeff Reisman · Accepted Answer

@keesuri25  You can suppress error messages by wrapping your formula in IFERROR:

=IFERROR(NETWORKDAY([PoC start date]@row, [PoC End date]@row), "")

This replaces any error messages with "", aka a blank cell.

Sometimes you might want a formula to not even act at all on any blank date fields, to avoid error messages altogether. In that case, you can use something like this that uses ISDATE to verify the values are dates before running the NETWORKDAY function:

=IF(AND(ISDATE([PoC start date]@row), ISDATE([PoC End date]@row)), (NETWORKDAY([PoC start date]@row, [PoC End date]@row)), "")

In English: If both fields are date values, run this NETWORKDAY formula, otherwise, leave this cell blank.