Networkday shows #INVALID DATA TYPE but prefer to show 0 or blank
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.
Best Answer
-
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)))
Answers
-
Use IFERROR for this. =IFERROR(NETWORKDAY([Start Date]@row, [End Date]@row),0)
-
Thanks Brian, that did it! I have a feeling that I'm going to spend a lot of time in this community.🙏
-
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)))
-
Thanks Kelly. Much appreciated!
Help Article Resources
Categories
Check out the Formula Handbook template!