Formula not working Parent Row

Luke York

Hi All,

The formula we have implemented on our programs to automate the colour dots does not work on the Parent row due the summary of the days being calculated from the first start to the last finish - thus the duration is technically "incorrect". I understand however that is just how smartsheet has set up the program to run in terms the of Parent rows. However, if anyone has any work arounds or possible suggestions to have the formula work correctly on the parent row too, please let me know.

See a screenshot of an example as well as the formula below:

=IF(START@row = "", "", IF(AND(START@row > TODAY(), [%]@row = 0), "", IF((AND(TODAY() > FINISH@row, IF(TODAY() = START@row, 0, IF(TODAY() > FINISH@row, 1, IF(AND(TODAY() > START@row, TODAY() <= FINISH@row), (NETWORKDAYS(START@row, TODAY() - 1) / t@row), 0))) > [%]@row)), "Red", IF(OR(AND(TODAY() >= FINISH@row, [%]@row = 1), (IF(TODAY() = START@row, 0, IF(TODAY() > FINISH@row, 1, IF(AND(TODAY() > START@row, TODAY() <= FINISH@row), (NETWORKDAYS(START@row, TODAY() - 1) / t@row), 0))) = 1)), "Green", IF(IF(TODAY() = START@row, 0, IF(TODAY() > FINISH@row, 1, IF(AND(TODAY() > START@row, TODAY() <= FINISH@row), (NETWORKDAYS(START@row, TODAY() - 1) / t@row), 0))) <= [%]@row, "Blue", IF((IF(TODAY() = START@row, 0, IF(TODAY() > FINISH@row, 1, IF(AND(TODAY() > START@row, TODAY() <= FINISH@row), (NETWORKDAYS(START@row, TODAY() - 1) / t@row), 0)))) - [%]@row <= 0.2, "Yellow", "Red"))))))

Logic:

- Red: if past due OR variance greater than 20%

- Yellow: if variance between 0-20%

- Blue: if on track (also if > 0% but only starting at future date - for the error report/conditional format)

- Green: activity is in the past+100% OR activity today and marked at 100%

Kind regards,

Luke

Genevieve P.

Hi @Luke York

How would you like the Parent's Status ball to update?

If you want the Parent row to just be blank, you can add that in to the first criteria, like so:

=IF(OR(START@row = "", COUNT(CHILDREN()) > 0), ""

Or you could have it say something like "Parent" instead of showing a status:

=IF(COUNT(CHILDREN()) > 0, "Parent"

If this isn't what you want, could you identify the Logic specific for the Parent row?

Thanks!

Genevieve