# Nested IF formula for children rows only

✭✭✭✭

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

• ✭✭✭✭✭✭
edited 02/16/23

I hope you're well and safe!

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

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

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!

• ✭✭✭

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)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!