Formula to return the EARLIEST DATE in an unbroken/consecutive sequence

Lauren Dominique

I am trying to write a formula to return the EARLIEST DATE in a consecutive sequence. If the sequence is broken, I want the formula to return the earliest date that the sequence was re-established.

I've included a screenshot of my sheet below.

I want the earliest date that "Minimum Requirements Met" was "Yes". If it ever became "No", I no longer want to pull the first date it was "Yes" - instead I want it to pull the first date it was "Yes" AFTER it was no longer "No."

In the screenshot below, I am using the below MIN formula to return the earliest date, but I am struggling with how to expand this to honor the breaks. When working correctly, row 1&2 in the screenshot below would have 04/04/2025 documented in the "Consecutively Green Since" column.

Thoughts?

Consecutively Green Since Column Formula: =MIN(COLLECT(Date:Date, [Minimum Requirements Met]:[Minimum Requirements Met], "Yes"))

Adam Murphy

Interesting one, try something like this:

=MIN(COLLECT([Date]:[Date], [Minimum Requirements Met]:[Minimum Requirements Met], "Yes", [Date]:[Date], >(MAX(COLLECT([Date]:[Date], [Minimum Requirements Met]:[Minimum Requirements Met], "No")))))

Adam Murphy

Interesting one, try something like this:

=MIN(COLLECT([Date]:[Date], [Minimum Requirements Met]:[Minimum Requirements Met], "Yes", [Date]:[Date], >(MAX(COLLECT([Date]:[Date], [Minimum Requirements Met]:[Minimum Requirements Met], "No")))))

Lauren Dominique

This is it!!!! Thank you Adam!

https://community.smartsheet.com/discussion/comment/481754#Comment_481754