Join Collect with Multiple Criteria

I have been searching the community posts for a solution, but have not been able to find one that works. I have a sheet that I need to combine multiple columns into one helper column. Here is the description of what I am trying to do (screenshot included below for reference)

If a date is less than 30 days from today, add the task name and the date to the helper column. The current formula I am using is: =JOIN(COLLECT(Task@row, June@row, <TODAY(30)))

However, it is only adding the task name to the helper column, not the date as well.

I actually need a formula that will look across the columns July through June and add the task and date if it is less than 30 days out.



Best Answer

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    Pulling the date from a date formatted column into a text column makes things a little more complicated. I have this working in a test sheet. I am assuming some of the dates in your image are for testing purposes? In the event there are multiple meetings of the same type (though it doesn't look like that could happen), this solution will display the date for the earliest one.

    =IF(COUNTIFS([July]@row:[June]@row, @cell >= TODAY(), [July]@row:[June]@row, @cell <= TODAY(30)) = 1, MONTH(MIN(COLLECT([July]@row:[June]@row, [July]@row:[June]@row, @cell >= TODAY(), [July]@row:[June]@row, @cell <= TODAY(30)))) + "/" + DAY(MIN(COLLECT([July]@row:[June]@row, [July]@row:[June]@row, @cell >= TODAY(), [July]@row:[June]@row, @cell <= TODAY(30)))) + "/" + YEAR(MIN(COLLECT([July]@row:[June]@row, [July]@row:[June]@row, @cell >= TODAY(), [July]@row:[June]@row, @cell <= TODAY(30)))) + " " + [Task]@row, "")


  • Thanks for your work on this! However, I am not able to get it to work. When I copy and paste the formula into my sheet, the cell returns blank.

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓

    Hi @Paula Reynolds

    If you are willing to add another helper column, you can do it this way.😀

    Helper Column 1

    =JOIN(COLLECT(July@row:June@row, July@row:June@row, ISDATE(@cell), July@row:June@row, ABS(@cell - TODAY()) < 30))

    Helper Column 2

    =IF(ISTEXT([Helper Column 1]@row), Task@row + " " + [Helper Column 1]@row)


  • Thank you, jmyzk_cloudsmart_jp!

    The first formula for Helper Column 1 works beautifully. The formula for Helper Column 2 did not work for me, but you got me in the right direction! I used the formula below in Helper Column 2 to join the two columns I needed together. I had to make sure that Helper Column 1 was a Date column.

    =JOIN((Task@row + " " + [Helper Column]@row))


    THANK YOU SO MUCH!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!