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

Options

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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!