Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

    ✭✭
    in Formulas and Functions

    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.

    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • Community Champion
      Answer ✓

      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))

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion
      Answer ✓

      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))

      · Share on FacebookShare on Twitter
    • ✭✭

      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 :)!

      · Share on FacebookShare on Twitter
    • Community Champion

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

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions