Join Function + Date

aya_i

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

Genevieve P.

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

aya_i

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.

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

aya_i

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