Formula Assistance
Formula Gurus  I need a formula that can be applied as a column formula in my column named Actual Duration (Days).
 IF the row is a PARENT row, the formula should return the sum of its children rows.
 For NONPARENT rows, the formula should calculate the days between the Act. Start and Act. Finish columns.
 IF there is an Act. Start but Act. Finish = NA, the formula should count the number of days between Act. Start and TODAY.
 IF both Act. Start and Act. Finish, the formula should return 0.
Your help is much appreciated!
Answers

Are you able to finish out that last piece of logic?
 IF both Act. Start and Act. Finish (are what?), the formula should return 0.
Assuming you meant "if both are blank", your formula would look something like this:
=IF(COUNT(CHLDREN([Primary Column Name]@row)) = 0, IF([Act. Start]@row <> "", IF([Act. Finish]@row = "NA", TODAY(), [Act. Finish]@row)  [Act. Start]@row, 0), SUM(CHILDREN()))

Thanks, Paul! Your assumption is correct.
When I entered the formula in an individual cell, it returned #UNPARSEABLE and when I tried to convert it to a column formula it returned a syntax error (below). Any ideas?

Make sure the column names in the formula are the column names being used in your sheet.

I realized that yesterday and super appreciate your response! One last question. Below is the formula (working perfectly!).
Could you please advise on what the formula would be if I wanted it to return the number of working days rather than calendar days, and then deduct 8 days from that?
=IF(COUNT(CHILDREN([Task Name]@row)) = 0, IF([Act. Start]@row <> "", IF([Act. Finish]@row = "", TODAY(), [Act. Finish]@row)  [Act. Start]@row, 0), SUM(CHILDREN()))

Try this:
=IF(COUNT(CHILDREN([Task Name]@row)) = 0, IFERROR(NETWORKDAYS([Act. Start]@row, IF([Act. Finish]@row = "", TODAY(), [Act. Finish]@row)), 0), SUM(CHILDREN()))
Help Article Resources
Categories
Check out the Formula Handbook template!