Formula that brings a parent row into a report based on certain date criteria

Pete R

I'm trying to bring parent rows into a report based on if the child row End Date is within the next 30 days. I've created a helper column but I'm struggling with the formula. My sheet has an Activities column, Start and End Dates etc and Im trying to create a formula that checks the helper column box of any row that has a date in the next 30 days but also checks any parent to that row.

Any help is much appreciated.

jmyzk_cloudsmart_jp

Hi @Pe

https://app.smartsheet.com/b/publish?EQBCT=3fe4d61324fa4a4eb320a60c21f30ffb (Link to the published demo sheet)

ANC =COUNT(ANCESTORS())
End Date in Next 30 Days =IF(ANC@row > 0, IF(ISDATE([End Date]@row), IF([End Date]@row < TODAY(30), 1)))
Flag Parent =IF(COUNTIF(CHILDREN([End Date in Next 30 Days]@row), 1) > 0, 1)

Pete R

Thats great, thank you. Is there a way to lose the Flag parent and ANC helper columns and put all the formulas into one in the End Date in Next 30 days please?

jmyzk_cloudsmart_jp

Hi @Pete R

Here is the formula.

[End Date in Next 30 Days] =IF(ANC@row > 0, IF(ISDATE([End Date]@row), IF([End Date]@row < TODAY(30), 1)), IF(COUNTIF(CHILDREN([End Date in Next 30 Days]@row), 1) > 0, 1))

By the way, I mixed up the Smartsheet link with the link for the other question in the previous comment. Here is the correct link.

https://app.smartsheet.com/b/publish?EQBCT=72776544acaf4652b95bbd4b19d0d163 (Link to the published demo sheet)