COLLECT with Date criteria

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 ✓

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!