Join Function + Date

Hello, I am trying to have row 1 reflect the outcome stated below and am struggling to get the right formula. I am currently using =JOIN(CHILDREN(), DATEONLY([Start Date]@row)) but want the start dates to reflect each of the corresponding Start Dates with Descriptions from the child row -- not the parent row. You can see that it's working for each of the Descriptions right now, but all the Start Dates show what's in the parent row (10/6/21).

Outcome desired: Description2 Start Date 2, Description 3 Start Date 3, Description 4 Start Date 4, Description 5 Start Date 5.

I am planning on using this formula for multiple parts of a greater grid, so hoping there's a consistent formula, rather than one that requires noting each row number.

Thank you for your help!

Aya

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @aya_i


    1) DESCRIPTION COLUMN

    In your first formula, the DATEONLY([Start Date]@row) is being used as the delimiter for the JOIN function, so it's adding in the date as the separator between each of the Children values.

    There isn't a way to parse out each Child Date next to its name by referencing the columns in a JOIN like this. The way I would set it up is to have a helper column (that you can Hide in the sheet after) which puts your Start Date and Description together in one cell per child row:

    =Description@row + " - " + [Start Date]@row


    Now that you have the values next to each other in one cell, you can use this as the initial range in your JOIN function:

    =JOIN(CHILDREN([Helper Column]@row), ", ")

    Here's how it looks on my sheet:

    They pale Helper Column is the one I would hide with the Column Formula applied.


    2) TEAMS IMPACTED

    For your Teams Impacted column, we can add a DISTINCT function so it only pulls back unique team names, like in my image above:

    =JOIN(DISTINCT(CHILDREN()), CHAR(10))

    I also used CHAR(10) as the delimiter so that it created separate values in the multi-select column. However this will only work if ONE selection is made per child row.


    Let me know if all of this makes sense and if it will work for you!

    Cheers,

    Genevieve

Answers

  • Sorry, one more question. I have drop down values under Teams Impacted, and would like the parent row to list each selection separately, and not include repeats. I currently just have =JOIN(CHILDREN()) but is there a way to have row 1 listed as AMER APAC Global without APAC being listed twice? Not sure if there's also a way to keep these drop down values separately so AMER appears in its own text bubble separate from APAC and Global.

    Thanks again.

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @aya_i


    1) DESCRIPTION COLUMN

    In your first formula, the DATEONLY([Start Date]@row) is being used as the delimiter for the JOIN function, so it's adding in the date as the separator between each of the Children values.

    There isn't a way to parse out each Child Date next to its name by referencing the columns in a JOIN like this. The way I would set it up is to have a helper column (that you can Hide in the sheet after) which puts your Start Date and Description together in one cell per child row:

    =Description@row + " - " + [Start Date]@row


    Now that you have the values next to each other in one cell, you can use this as the initial range in your JOIN function:

    =JOIN(CHILDREN([Helper Column]@row), ", ")

    Here's how it looks on my sheet:

    They pale Helper Column is the one I would hide with the Column Formula applied.


    2) TEAMS IMPACTED

    For your Teams Impacted column, we can add a DISTINCT function so it only pulls back unique team names, like in my image above:

    =JOIN(DISTINCT(CHILDREN()), CHAR(10))

    I also used CHAR(10) as the delimiter so that it created separate values in the multi-select column. However this will only work if ONE selection is made per child row.


    Let me know if all of this makes sense and if it will work for you!

    Cheers,

    Genevieve

  • Both worked, and were so helpful -- thank you Genevieve!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!