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

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.

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
How to Create Time Duration
I have a report where I need a formula to figure out time duration. For example, employee starts at 8:00 am and ends at 9:00 am, I need the next column to tell me 1.00 to indicate he worked 1 hour. I will be using military time throughout this sheet.
Formula with range data
Hello, I'm looking to see if anyone knows a formula that would add owner income and co-owner income together to reflect in Joint Income column. I'm not sure how to do this with data in ranges. Thank you.