Formula to return the EARLIEST DATE in an unbroken/consecutive sequence
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"))
If this comment helped you, please help me and help others by using the buttons below if you found it 💡 Insightful or ❤️ Awesome!
Best Answer
-
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")))))
Answers
-
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")))))
-
This is it!!!! Thank you Adam!
If this comment helped you, please help me and help others by using the buttons below if you found it 💡 Insightful or ❤️ Awesome!
Help Article Resources
Categories
Check out the Formula Handbook template!