Getting Oldest Date based on another field

Kristie Diersen

I have created a Metrics Sheet to roll up the oldest date based on another field. I am trying to have it return the oldest date in the "Date Sent to CSOS" column if the Date approved by CSOS column is blank. I can also use the if CSOS Review column is Submitted but I can't get either one to work properly.

Does anyone have any ideas on how to get this formula to work?

Paul Newcome

@Genevieve P I'm struggling to see how this would work. The MATCH function would pull a number for a single cell, so the MIN function would only be looking at a single cell so there would be nothing for the MIN to compare. Only a singe point of data.

I tried testing it (ranges on the same sheet), and ended up with an #INVALID VALUE error.

I was however able to get a MIN/COLLECT to work.

=MIN(COLLECT([Date Sent to CSOS]:[Date Sent to CSOS], [Date approved by CSOS]:[Date approved by CSOS], @cell = ""))

=MIN(COLLECT({Date Sent to CSOS in other sheet}, {Date approved by CSOS in other sheet}, @cell = ""))

Genevieve P.

Hi Kristie,

Are you just looking for the overall oldest date in that column (doesn't matter what the title is), but only if the "approved" column is blank?

If so, you could use an INDEX(MATCH formula to look if the approved column is blank, then return the MIN date. This is how it would look within the current sheet:

=INDEX(MIN([Date Sent to CSOS]:[Date Sent to CSOS]), MATCH("", [Date approved by CSOS]:[Date approved by CSOS]))

In a summary sheet you would need to use cross-sheet references:

=INDEX(MIN({Date Sent to CSOS in other sheet}), MATCH("", {Date approved by CSOS in other sheet}))

Here are some Help Articles I used: INDEX function, MATCH function, MIN function, Cross Sheet References

Let me know if this works for you!

Genevieve

Paul Newcome

@Genevieve P I'm struggling to see how this would work. The MATCH function would pull a number for a single cell, so the MIN function would only be looking at a single cell so there would be nothing for the MIN to compare. Only a singe point of data.

I tried testing it (ranges on the same sheet), and ended up with an #INVALID VALUE error.

I was however able to get a MIN/COLLECT to work.

=MIN(COLLECT([Date Sent to CSOS]:[Date Sent to CSOS], [Date approved by CSOS]:[Date approved by CSOS], @cell = ""))

=MIN(COLLECT({Date Sent to CSOS in other sheet}, {Date approved by CSOS in other sheet}, @cell = ""))

Genevieve P.

Ohmygoodness, my apologies! I tested and thought it worked but had the wrong data in there so it looked like a match but wasn't.

Thanks for the rescue, Paul!! MIN(COLLECT is definitely the right answer.

Kristie Diersen

I just got it to work! Thank you so much to the both of you for your assistance. I never would have figured this out.

Paul Newcome

@Genevieve P Haha. No worries. I've done the same thing plenty of times before.

@Kristie Diersen Happy to help! 👍️