Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Sum children networkdays

I am working on a complex formula that I won't get into the details of but I am having a problem with one small part of it.


I need to check the children of a parent row to see if the task has already started but not ended. Column names are {Actaul Start] and [Actual Finish]. Then if this criteria is met I want to get the NETWORKDAYS between the finish day and TODAY() for each of the children that meet that criteria and then SUM them. I would perfer to do this without creating a bunch of new columns.


Any help? Thanks.


  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    I don't believe you will find a solution that does not have at least one new column, as the SUM, SUMIF, and SUMIFS will not sum calculated values


    Your requirement also does not account for overdue tasks. Those will throw off your calcualations.


    I assume:

    1. you are looking at

    =NETWORKDAYS(TODAY(), [Actual Finish]23)

    which is positive for dates in the future, negative for dates in the past.

    2. finished is based the finish date, not on a Completed column of some sort


    Based on that, this would go into the new column:


    =IF(TODAY() >= [Actual Start]23, IF(TODAY() < [Actual Finish]23, MAX(NETWORKDAYS(TODAY(), [Actual Finish]23), 0), 0), 0)


    for row 23.


    The parent is just =SUM(CHILDREN())


    This formula also works to show why a non-past due task is not part of the sum:


    =IF(TODAY() >= [Actual Start]58, IF(TODAY() < [Actual Finish]58, MAX(NETWORKDAYS(TODAY(), [Actual Finish]58), 0), "Started and Finished"), "Not Started")


    Hope that helps




  • The past due tasks are handled in the larger part of the formula that I mentioned. I will see how this formula works in. Adding new columns to all of our smartsheets would be a huge pain and I would like to avoid it but I may not be able to. Its too bad you can't set variables in a formula.

This discussion has been closed.