Networkday shows #INVALID DATA TYPE but prefer to show 0 or blank

Xavier Gomez

I'm using =NETWORKDAY([Start Date]@row, [End Date]@row) to show the duration between a start date and an end date which works fine when I enter dates into those cells.

However, without actual dates, the cell shows #INVALID DATA TYPE. The second part of the problem is that in the top row of the column, I want to add the total number of days for each subtask hence the =SUM(CHILDREN()) formula in the top cell of the column. This cell shows #INVALID DATA TYPE if the cells below it contain anything other than a number.

So the ask is: What can I do to show 0 or blank in the duration field while keeping the formula =NETWORKDAY([Start Date]@row, [End Date]@row) active? If I can do that, I figure that the top cell of the column will show a number rather than the error message.

Thanks in advance.

Kelly Moore

Hello @Xavier Gomez

Building on Brian's answer, if you wanted a single formula that would work for both your Parent rows and Child rows, consider the following:

=IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), IFERROR(NETWORKDAY([Start Date]@row, [End Date]@row), 0))

Here's another way that avoids errors:

=IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), IF(AND(ISDATE([Start Date]@row), ISDATE([Milestone Date (Actual)]@row)), NETDAYS([Start Date]@row, [Milestone Date (Actual)]@row)))

Brian_Richardson

Use IFERROR for this. =IFERROR(NETWORKDAY([Start Date]@row, [End Date]@row),0)

Xavier Gomez

Thanks Brian, that did it! I have a feeling that I'm going to spend a lot of time in this community.🙏

Kelly Moore

Hello @Xavier Gomez

Building on Brian's answer, if you wanted a single formula that would work for both your Parent rows and Child rows, consider the following:

=IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), IFERROR(NETWORKDAY([Start Date]@row, [End Date]@row), 0))

Here's another way that avoids errors:

=IF(COUNT(CHILDREN()) > 0, SUM(CHILDREN()), IF(AND(ISDATE([Start Date]@row), ISDATE([Milestone Date (Actual)]@row)), NETDAYS([Start Date]@row, [Milestone Date (Actual)]@row)))

Xavier Gomez

Thanks Kelly. Much appreciated!