For Network Days formula how do I ignore blank date rows

keesuri25

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

Try an IFERROR like so:

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

Jeff Reisman

@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.

Paul Newcome

Try an IFERROR like so:

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

Jeff Reisman

@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.

keesuri25

Thank you both for the prompt responses. Works like a charm :D

Jeff Reisman

@Paul Newcome You gotta love the symmetry of the presentation here! 😂