Index/Collect Two Separate Dates and Compare Them to Return Highest Date

paige_pj

I'm trying to use Index Collect based on a client name to look at two different dates in a separate sheet, and compare them to pull the highest date.

We have 1 sheet that we log all contacts, and there are 2 types of contacts "Type1" and "Type2". Then, we have an overall summary sheet that shows the most recent contact. Currently, I'm only looking at Type 1 when I'd like to look at both.

The individual Index Collect would be:

=INDEX(COLLECT({Date_Engagement}, {Type1_Engagement}, 1, {ClientName_Engagement}, [ClientName]@row), 1)

=INDEX(COLLECT({Date_Engagement}, {Type2_Engagement}, 1, {ClientName_Engagement}, [ClientName@row), 1)

In our overview sheet, I want it to Collect the most recent date into 1 cell. So, I need to compare the two individual index/collects and pull the date closest to today.

Carson Penticuff

It may not be the most elegant solution, but this will just compare the output of the two different formulas you posted and use the one with the latest date.

=IF(INDEX(COLLECT({Date_Engagement}, {Type1_Engagement}, 1, {ClientName_Engagement}, [ClientName]@row), 1) > INDEX(COLLECT({Date_Engagement}, {Type2_Engagement}, 1, {ClientName_Engagement}, [ClientName@row), 1),INDEX(COLLECT({Date_Engagement}, {Type1_Engagement}, 1, {ClientName_Engagement}, [ClientName]@row), 1), INDEX(COLLECT({Date_Engagement}, {Type2_Engagement}, 1, {ClientName_Engagement}, [ClientName@row), 1))

Carson Penticuff

It may not be the most elegant solution, but this will just compare the output of the two different formulas you posted and use the one with the latest date.

=IF(INDEX(COLLECT({Date_Engagement}, {Type1_Engagement}, 1, {ClientName_Engagement}, [ClientName]@row), 1) > INDEX(COLLECT({Date_Engagement}, {Type2_Engagement}, 1, {ClientName_Engagement}, [ClientName@row), 1),INDEX(COLLECT({Date_Engagement}, {Type1_Engagement}, 1, {ClientName_Engagement}, [ClientName]@row), 1), INDEX(COLLECT({Date_Engagement}, {Type2_Engagement}, 1, {ClientName_Engagement}, [ClientName@row), 1))

paige_pj

Thank you! Most of my solutions are not elegant…I ended up having to wrap it in 2 separate IFERRORS due to other things I didn't think about :)!

Carson Penticuff

I'm glad it's working for you, and IFERRORS are pretty much a staple when working with dates.