Formula needs to ignore parents

Hi All


I have a formula that puts a RAG status depending on when the task is due:


=IF([% Complete]@row = 1, "Blue", IF(Finish@row > TODAY(+7), "Green", IF(AND(Finish@row >= TODAY(+3), Finish@row < TODAY(+7)), "Yellow", IF(Finish@row < TODAY(+3), "Red", "Nope"))))


It works fine but copied down it applies the formula to every row. I would like it to only apply the formula against actual tasks/children, and not against any parent or summary tasks. I do appreciate that it is useful to see that the parent summary also has red tasks in it, but the purpose of this for me is to


a) count the tasks I have that are R,Y,G and count only the tasks. I therefore want to exclude parents.


Should I be trying to do with more with a countif (i.e. count if red and it not a parent?)


This enables me to report that I have 50 tasks, 10 are due this week, ten are due next week and 10 beyond that (just an example, I know my formula is 3-6. +7 etc!!)


I hope this makes sense.

Thank you!!

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hello!


    You could try doing something like this:

    =if(count(children())>0,"",IF([% Complete]@row = 1, "Blue", IF(Finish@row > TODAY(+7), "Green", IF(AND(Finish@row >= TODAY(+3), Finish@row < TODAY(+7)), "Yellow", IF(Finish@row < TODAY(+3), "Red", "Nope")))))

    This should make the field display as blank if there are any children rows. However, if any of your "children" rows have additional subtasks, this cell would be blank for them, too.

    Hope this helps!


    Best,

    Heather

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hello!


    You could try doing something like this:

    =if(count(children())>0,"",IF([% Complete]@row = 1, "Blue", IF(Finish@row > TODAY(+7), "Green", IF(AND(Finish@row >= TODAY(+3), Finish@row < TODAY(+7)), "Yellow", IF(Finish@row < TODAY(+3), "Red", "Nope")))))

    This should make the field display as blank if there are any children rows. However, if any of your "children" rows have additional subtasks, this cell would be blank for them, too.

    Hope this helps!


    Best,

    Heather

  • That's amazing, worked perfectly Thank you Heather!

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Fantastic! Glad it worked. Have a great weekend!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!