The smallest date with a specific status in the child cell

mpiza

Hello community, I hope this message finds you well.

I need a formula to classify as TRUE if my row has a "Not Started" status and presents the smallest start date within the cells, which in the example figure, are filled in white. However, I couldn't come up with an efficient result.

Any tips or help to build this formula will greatly assist me.

Thank you very much.

My expected result for this image is for rows 9 and 12 to receive TRUE in a new column.

KPH

I would add a column to the sheet with a formula to identify the parent for each row. This formula would return the value of the Task Name* column for the parent, which will be Step 1, Step 2, etc.

=PARENT([Task Name]@row)

* You'll need to use whatever your column heading is.

Then use this identifier in the COLLECT to only collect dates from rows that share a parent. You can then reference the entire column rather than specific rows.

=IF([Start Date]@row = MIN(COLLECT([Start Date]:[Start Date], Status:Status, "Not Started", Step:Step, Step@row)), "TRUE")

KPH

Hi @mpiza

Tips!

You need an IF function to return the TRUE.

You need this to evaluate if the start date on the current row is equal to the MIN start date in that section. A COLLECT function will return all the dates in a section if the status is "Not Started". You can use the COLLECT to feed into your MIN.

So your formula would be in the form

=IF(column@row=MIN(COLLECT(....)),"TRUE")

By "within the cells" do you mean the sibling group - all the rows under one parent, so 6-9 and 10-13 in your example? And if so, will this always be a sibling group? If so you can use this relationship to define the range to check in your COLLECT and won't need to adjust the row numbers each time.

Otherwise, you will need to fix the row numbers in the COLLECT and it will look something like:

COLLECT([Start Date]$6:[Start Date]$9, Status$6:Status$9, "Not Started")

mpiza

https://community.smartsheet.com/discussion/comment/430974#Comment_430974

Thank you for the tips, @KPH

Yes, I'm referring to the sibling group, and this group varies, mainly with the inclusion of new lines, so I believe that COLLECT would be the ideal function and that it be automated in a formula column, so that the result is always up to date.

COLLECT([Start Date]$6:[Start Date]$9, Status$6:Status$9, "Not Started")

For this case, COLLECT would have to be used to separate each sibling group, and then identify the smallest date, and this has been my main problem.

Would this have a soluction?

mpiza

Thank you for the tips, @KPH

Yes, I'm referring to the sibling group, and this group varies, mainly with the inclusion of new lines, so I believe that COLLECT would be the ideal function and that it be automated in a formula column, so that the result is always up to date.

COLLECT([Start Date]$6:[Start Date]$9, Status$6:Status$9, "Not Started")

For this case, COLLECT would have to be used to separate each sibling group, and then identify the smallest date, and this has been my main problem.

Would this have a soluction?

KPH

I would add a column to the sheet with a formula to identify the parent for each row. This formula would return the value of the Task Name* column for the parent, which will be Step 1, Step 2, etc.

=PARENT([Task Name]@row)

* You'll need to use whatever your column heading is.

Then use this identifier in the COLLECT to only collect dates from rows that share a parent. You can then reference the entire column rather than specific rows.

=IF([Start Date]@row = MIN(COLLECT([Start Date]:[Start Date], Status:Status, "Not Started", Step:Step, Step@row)), "TRUE")

mpiza

Awesome, @KPH

You solved the issue. Thank you very much!!

KPH

Glad I could help.