Multiple dates from one row in calendar

Leeba Z.

Hi,
I'm tracking clients in a sheet with about 12 different date columns per row. I'd like each client's name to appear on the calendar by every date listed in their row, but Smartsheet only allows one date column per calendar view. Are there any good workarounds for this?

kowal

hi @Leeba Z.

so following the logic in calendar view 2 dates are only important Start Date and End Date so based on that it can show you where something starts and ends.

If you want to see more dates in calendar view those can be only visble as labels so you can configure it in Gantt settigns what shall be the label for the basrs. I assume for you it's now client name. What I recommend is to create Helping columns like "Info for Label" and add there information with text formulas like =ClientName@Row +""+DateofDelivery@Row+" "+DateofSending@row etc. etc. so of course I made up now the name of the columns…. but what is important you can have many dates with the client name as label for the calendar view bars.

Paul Newcome

You could create a separate sheet that has each date listed in a single column. Using a formula with cross sheet references such as JOIN/COLLECT should allow you to essentially create a list of all clients for each date.

Leeba Z.

HI @Paul Newcome, would this work if I am constantly adding a lot of new clients to my sheet? Or does that helper sheet need to be set up for each specific client beforehand?

see example below, how would you set up the helper sheet?

Thanks!

Georgie

Hi @Leeba Z.,

Following @Paul Newcome's great advice, I set up a helper sheet and tested this. I found that the formula below works in my sheet to pull in the names of all clients who have each date in their row:

• =JOIN(COLLECT({Name}, {Date 1}, Date@row), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Name}, {Date 2}, Date@row), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Name}, {Date 3}, Date@row), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Name}, {Date 4}, Date@row), CHAR(10)) + CHAR(10) + JOIN(COLLECT({Name}, {Date 5}, Date@row), CHAR(10))

As Paul suggested, my helper sheet contains a date column named “Date” with all dates listed, one per row. 

I then used the above formula in the Primary column (since Calendar view uses the Primary column as the display value), where {Name} is the entire Client Name column in the source sheet and {Date 1} is the entire Date 1/Week 1 column in the source sheet, {Date 2} is the Week 2 column, and so on. To find out more about creating cross sheet-references, check out this help article: Create cross-sheet references.

As an example, my source sheet looks like this:

And you can see my helper sheet with the formula below:

Yours will of course show the Client Names from your source sheet rather than simply “Name 1” etc!

The use of “CHAR(10)” in the formula enters a line break - ensure that you apply the wrap formatting to the Client Name column so that the names are displayed with line breaks.

You can then switch the helper sheet to Calendar View to see all client names for each date.

Does that work for you?

Georgie

Leeba Z.

Thank you! That did work, however see below snip how it shows up the calendar (not so great), any ideas how I can get it to show all the names or each name as a separate bubble?

If not will use this workaround and will just have to click on it to see the names for that date.

Paul Newcome

How many possible names could you have on a single date, and what range of dates would you want?

Technically, each person as a separate bubble is possible, but it doesn't scale well. The basic idea is that you would need a separate row for every date/name combo. So if there are 10 names per date and 30 dates (1 month for example), you are already looking at 300 rows.

jmyzk_cloudsmart_jp

Hi @Leeba Z.

Thanks to you and other commentators, I guess I understand the scenario.

Here is a demo solution for a 12-stage date for a 12-client case, which needs 144 rows.

https://app.smartsheet.com/b/publish?EQBCT=375b1b7051b246b1afbeaa8a4b0adba1 (View this demo published sheet in calendar view. Test the filter.)

First, I created a Client 12-Stage Interaction Timeline Sheet, using fictitious names and random dates in chronological order.

https://app.smartsheet.com/b/publish?EQBCT=b4b02f8e27a54eaca4f74796ad175282

Then, I created a Client 12 Stages Interaction Calendar Sheet as shown below, with the Primary column, [Client Interaction], populated with Client Name + ":" + Stages format with the following formula;

[Client Interaction]

=IF(INT((Row@row + Stages# - 1) / Stages#) <= Clients#, INDEX({Client Interaction Timeline : Client Name}, INT((Row@row + Stages# - 1) / Stages#)) + ": " + INDEX({Interaction Stages}, MOD(Row@row - 1, Stages#) + 1))

In the formula, INT((Row@row + Stages# - 1) / Stages# gives a client's index incrementing every 12 rows.

Similarly, MOD(Row@row - 1, Stages#) + 1 gives Stages indexes 1 to 12 based on the Row@row value.
With the index, the formula looks up the Interaction 12 Stages sheet as shown in the 3rd image below.)

The formula for the date is as follows: using the same index, the first as the client row, and the second for the corresponding stage date column.

=INDEX({Client Interaction Timeline : Interaction Dates}, INT((Row@row + Stages# - 1) / Stages#), MOD(Row@row - 1, Stages#) + 1)

https://app.smartsheet.com/b/publish?EQBCT=375b1b7051b246b1afbeaa8a4b0adba1

https://app.smartsheet.com/b/publish?EQBCT=5cef138b3f354b8dad17e0d9b441ba36

I hope this solution helps visualize various stages of each customer in a calendar format—happy to refine or discuss further if needed!