Finding the earliest date in rows, contingent on another column's value

Ben Donahue

I am trying to return the earliest date in a Date Column where another column does not contain "Done".

Simple statement, but I am completely flummoxed.

Additionally, I need to return this date to a non-date column. I am using this formula to grab the earliest date that appears in the entire column in question:

=IF(MONTH(MIN(Start:Start)) < 10, "0") + MONTH(MIN(Start:Start)) + "/" + IF(DAY(MIN(Start:Start)) < 10, "0") + DAY(MIN(Start:Start)) + "/" + YEAR(MIN(Start:Start))

Even starting with that, I just don't know where to go.

The use case here is I have a to-do list and have several tasks completed. I want these tasks not to count as a start date for my reporting.

Your help is appreciated.

Paul Newcome

You will want to include a COLLECT within your MIN.

 

=MIN(COLLECT(Date:Date, Status:Status, @cell <> "Done"))

 

This will give you the date to work with.

Ben Donahue

Oooo, COLLECT is a new one to me. Thank you, Paul. You've come through yet again!

Paul Newcome

Happy to help! 

 

COLLECT is a GREAT function. It provides a ton of flexibility once you get used to using it. It is definitely something I recommend playing around with as it opens up a ton of new possibilities within formulas.

Ben Donahue

Thank you, Paul. You are a wealth of help and knowledge.

I took your model and applied it to my situation, and ended up with the following functional code:

=IF(MONTH(MIN(COLLECT(Start:Start, [Days till due]:[Days till due], @cell <> "Done", [Tier Level]:[Tier Level], =1))) < 10, "0") + MONTH(MIN(COLLECT(Start:Start, [Days till due]:[Days till due], @cell <> "Done", [Tier Level]:[Tier Level], =1))) + "/" + IF(DAY(MIN(COLLECT(Start:Start, [Days till due]:[Days till due], @cell <> "Done", [Tier Level]:[Tier Level], =1))) < 10, "0") + DAY(MIN(COLLECT(Start:Start, [Days till due]:[Days till due], @cell <> "Done", [Tier Level]:[Tier Level], =1))) + "/" + YEAR(MIN(COLLECT(Start:Start, [Days till due]:[Days till due], @cell <> "Done", [Tier Level]:[Tier Level], =1)))

This will collect the earliest date in column "Start" when "Days till due" is not "DONE" and "Tier Level" is equal to 1, with some date-based formatting.

My problem is solved. However, if you have any pointers or advice on how this could have been done better or more elegantly, I am always up for leveling up my game. 

Thank you, again, Paul.

Paul Newcome

To enter data into a column that is not designed for that data type, you can always convert it to a text string by using 

 

+ "" 

 

at the end of the formula.

 

Try taking your MIN/COLLECT formula and adding that to the end. Then just drop that into your text field.

.

=MIN(COLLECT(Start:Start, [Days till due]:[Days till due], @cell <> "Done", [Tier Level]:[Tier Level], =1)) + ""

.

What is the reason for specifically converting it to a text value?

Ben Donahue

I'm just trying to report a date value from a date column in a text column. Converting it to a string sounds WAY easier.



I have another question for you in relation to the COLLECT help you gave earlier. Now, I am trying to find the min value of CHILDREN in a column of numbers where that the value of the % Complete column is not = 100%, but, again, only for the CHILDREN. I will post this in the Community, also.

Paul Newcome

It would be the same exact idea. The key is in the syntax of the COLLECT function.

 

=COLLECT(range_to_collect, criteria_range_1, criteria_1, criteria_range_2, criteria_2, ....................)

.

Think of it as a SUMIFS except you're gathering data instead of adding numbers. You then want to take that data you have collected based on your specific criteria, and use THAT for the main function to look at.

.

So if you want to collect the children

 

COLLECT(CHILDREN(),

.

Where the children of that same column

COLLECT(CHILDREN(), CHILDREN(),

.

are less than 1 (100%)

COLLECT(CHILDREN(), CHILDREN(), @cell < 1)

.

and then find the lowest value

=MIN(COLLECT(CHILDREN(), CHILDREN(), @cell < 1))

.

Although I honestly think using the COLLECT function for this is not exactly necessary. Using MIN and less than within the criteria creates a redundancy. It won't break the formula, but really a straightforward

 

=MIN(CHILDREN())

 

would produce the same exact results.