Formula Assistance

CyberJL · 01/04/24

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 · 01/04/24

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 · 01/04/24

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 · 01/05/24

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

CyberJL · 01/05/24

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 · 01/08/24

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

Formula Assistance

Answers

Help Article Resources

Categories