Nested IF formula for children rows only

angelapaj

Hi,

I hope you will be able to help me.

I'm trying to write a formula that will change the task status, depending on the date. I have done the below formula and it is working great:

=IF([Actual Finish Date]@row < TODAY(), "Completed", IF([Estimated Finish Date]@row > TODAY(), "In Progress", IF([Estimated Finish Date]@row < TODAY(), "Overdue")))

However, I'd like this formula to affect only child rows and leave the parent ones blank. I would still like to use it as a column formula.

Can you please advise if it's possible and if so, how do I do it? I tried using CHILDREN function but it doesn't seem to work for me - most likely I'm just not using it in correct way.

I'd appreciate all the help.

Thanks

Andrée Starå

Hi @angelapaj

I hope you're well and safe!

Try something like this. (replace the column name with yours)

=IF(COUNT(CHILDREN([Task Name]@row)) > 0,"", your formula

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Razetto

You could add another column "Level" with the following formula =count(ancestors(primary@row)) so your parent row will be a 1 and the children a 2. If you have grandchildren then it'll show as a 3.

Assuming it's only two levels down, then your formula could be something like =IF(Level@row = "2", IF([Actual Finish Date]@row < TODAY(), "Completed", IF([Baseline Finish Date]@row > TODAY(), "In Progress", IF([Baseline Finish Date]@row < TODAY(), "Overdue"))), " ")

Hope it works!

CraigO

Another option you could use is creating a helper column to flag children rows and then use that in your column formula.

=IF(COUNT(ANCESTORS()) > 0, 1, 0)