Finding the earliest date from Children based on the status in other column

Hi all,
I'm trying to find the earliest (Min) date from children based on the value in a different column. So if the value in Asset Status column is not "Completed", "Cancelled" or "On Hold", then consider those children and then find the earliest date(min) from them.
The column where I'm trying to find the earliest date is a date column and Asset Status is a dropdown single select value column.
So Far I've tried:
=MIN(COLLECT(CHILDREN(), [Asset Status]:[Asset Status], @cell <> "Completed", [Asset Status]:[Asset Status], @cell <> "Cancelled", [Asset Status]:[Asset Status], @cell <> "On Hold", [Asset Status]:[Asset Status], @cell <> ""))
But I keep getting un parsable error.
I stumbled upon this thread and @Paul Newcome suggested similar pattern so I guess I'm on the right track?
Best Answer
-
Hi @ameyaa
I made a video showing how to solve this, view it at https://youtu.be/MNCmmavU2HU
You can use this formula in the cell of any parent row where you want to return the earliest date of all children in that row where status does not equal completed, on hold, cancelled, or blank.
=MIN(COLLECT(CHILDREN(), CHILDREN([Asset Status]@row), AND(@cell <> "Completed", @cell <> "On Hold", @cell <> "Cancelled", @cell <> "")))
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/π‘Insightful, β¬οΈ Vote Up, β€οΈAwesome!
Answers
-
Hi @ameyaa
I made a video showing how to solve this, view it at https://youtu.be/MNCmmavU2HU
You can use this formula in the cell of any parent row where you want to return the earliest date of all children in that row where status does not equal completed, on hold, cancelled, or blank.
=MIN(COLLECT(CHILDREN(), CHILDREN([Asset Status]@row), AND(@cell <> "Completed", @cell <> "On Hold", @cell <> "Cancelled", @cell <> "")))
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/π‘Insightful, β¬οΈ Vote Up, β€οΈAwesome!
-
Thanks @SoS | Dan Palenchar , I'll check it out and update you.
-
@SoS | Dan Palenchar it worked! Thank you.
-
Is there a way to include rows with blank statuses? In the example if the Status cell is blank the due date of that row is not included when calculating the minimum due date.
I tried removing @cell <> "" at the end but it didn't make any difference.
-
Hello @KPH,
I just saw this 2+ years later, sorry for the delay! I don't know if you still need this but I think this would work.
=MIN(COLLECT(CHILDREN(), CHILDREN([Asset Status]@row), OR(ISBLANK(@cell), AND(@cell <> "Completed", @cell <> "On Hold", @cell <> "Cancelled", @cell <> "")))
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/π‘Insightful, β¬οΈ Vote Up, β€οΈAwesome!
-
I did figure it out, but thanks for responding!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 208 Use Cases
- 517 Announcements
- 5.6K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 84 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!