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 <> "")))
👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner
If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!
- 🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus
- ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube
PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!
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 <> "")))
👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner
If this helped, help me & the SSC by accepting and reacting w/ 💡insightful, ⬆️ Vote Up, and/or ❤️Awesome!
- 🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus
- ▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube
PS - If you have a follow up response tag me @SoS | Dan Palenchar so I get notified of your reply!
-
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.
Help Article Resources
Categories
Check out the Formula Handbook template!