The smallest date with a specific status in the child cell
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.
Best Answer
-
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")
Answers
-
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")
-
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?
-
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?
-
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")
-
Awesome, @KPH
You solved the issue. Thank you very much!!
-
Glad I could help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
