Formula Assistance

CyberJL

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 NON-PARENT 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!

Paul Newcome

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()))

CyberJL

https://community.smartsheet.com/discussion/comment/411767#Comment_411767

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?

Paul Newcome

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

CyberJL

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()))

Paul Newcome

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()))