MAX(CHILDREN()) to roll up at parent level the latest date of a child

Hi all,

Could you help me a bit with the syntax of a formula.... I've been stuck for a while and can't get it right...

I'm calculating the Projected Finish Date at the task level and I want the Parent to roll up the maximum date from all its children.

[Projected Finish Date] =IFERROR([Status Update Date]@row + [How many days still required until Complete]@row, "Provide Updates")

[Status Update Date] is captured automatically by a workflow whenever a task owner updates its task. One of the updates requested is to specify [How many days still required until Complete] at the moment the status update is captured. It's configured this way because it's easier for people to provide status updates to estimate the remaining number of days to complete their tasks rather than approximate the % complete (which is more subjective).

I want the parents/ granparents etc to just roll up the latest Projected Finish Date from all children and have tried this formula (Version 1)

= IFERROR(IF(COUNT(CHILDREN()) > 0, MAX(CHILDREN()), ([Status Update Date]@row + [How many days still required until Complete]@row), "Provide Updates"), but I get "Incorrect Argument set" and "Blocked"

I've also tried this Version 2

=IF(COUNT(CHILDREN()) = 0, [Status Update Date]@row + [How many days still required until Complete]@row, MAX(CHILDREN()))

but I can't get the parents to roll up


Any pointers would be much appreciated!

Andreea

Answers

  • Kimberly Loveless
    Kimberly Loveless ✭✭✭✭✭✭

    I think if I am understanding it all correctly that you should be able to use

    =MAX(CHILDREN())

    If it is going in the test column then you would just need to reference the other column

    =MAX(CHILDREN([Projected Finish Date]@row))

  • Andreea Iuras
    edited 07/16/21

    Hi Kimberly,

    Thank you for your answer! Your solution works if I keep both the [Projected Finish Date] Column and the "test" column. I was wondering if I could just calculate everything within one column.

    Does that make sense?

    Andreea

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!