COLLECT with Date criteria

Options

Hello,


Here is the formula I need help with please:

=COLLECT({2046 - B1 Gantt Project Plan WPincomments}, {2046 - B1 Gantt Project Plan Task},= "FE Sign off required (On QASOR)", ({2046 - B1 Gantt Project Plan Date}, < DATE(2023, 7, 28)))

{2046 - B1 Gantt Project Plan WPincomments} is the column where the data I want to collect is

{2046 - B1 Gantt Project Plan Task} is the column where my 1st criteria can be found

= "FE Sign off required (On QASOR)" is my 1st criteria

{2046 - B1 Gantt Project Plan Date} is the column where my 2nd criteria can be found, a date column

< DATE(2023, 7, 28) is my second criteria

Thank you

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    edited 10/24/23 Answer βœ“
    Options

    You need to make a few alterations for JOIN to work:

    =JOIN(COLLECT({2046 - B1 Gantt Project Plan WPincomments}, {2046 - B1 Gantt Project Plan Task},"FE Sign off required (On QASOR)", {2046 - B1 Gantt Project Plan Date}, < DATE(2023, 7, 28)), "-")

    This should give you all the results in a single cell, separated by dashes.

    An example of it in action (with columns rather than cross sheet refs):

    Hope this helps, but if I've misunderstood something or you have any problems/questions then just post! πŸ™‚

Answers

  • KarineMul22_TPg
    KarineMul22_TPg ✭✭
    Options

    As COLLECT doesn't work on its own, I have added JOIN but still has the same issue


    =JOIN(COLLECT({2046 - B1 Gantt Project Plan WPincomments}, {2046 - B1 Gantt Project Plan Task},="FE Sign off required (On QASOR)", ({2046 - B1 Gantt Project Plan Date}, < DATE(2023, 7, 28))), "-")

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    edited 10/24/23 Answer βœ“
    Options

    You need to make a few alterations for JOIN to work:

    =JOIN(COLLECT({2046 - B1 Gantt Project Plan WPincomments}, {2046 - B1 Gantt Project Plan Task},"FE Sign off required (On QASOR)", {2046 - B1 Gantt Project Plan Date}, < DATE(2023, 7, 28)), "-")

    This should give you all the results in a single cell, separated by dashes.

    An example of it in action (with columns rather than cross sheet refs):

    Hope this helps, but if I've misunderstood something or you have any problems/questions then just post! πŸ™‚

  • KarineMul22_TPg
    KarineMul22_TPg ✭✭
    Options

    Thank you Nick, but it's still coming back with #UNPARSEABLE :(

    I suspect it's the date part but cannot find the right way

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    There was an extra bracket in there (just before the date range/criteria) - try the (now edited) formula.

  • KarineMul22_TPg
    KarineMul22_TPg ✭✭
    Options

    Brilliant, it works! Many thanks

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    No problem, glad to have helped!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!