Sum children networkdays

Joseph Tuccillo

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

Joseph,

 

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

 

Craig

 

Joseph Tuccillo

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.