Project Milestones on a single line as a string?

RayB

Hi Community

I have seen the posts by people trying to achieve multiple milestones on a single line - I have the same issue - and the question I wanted to pose was could I construct a string where the spaces between the milestone markers was based on the time between the milestones (for example, one text space per week) ? The concept sounds simple and the basic calculations of time between milestones is straight forward but I can't work out how to create the length of blank spaces between milestone symbols to build the string without using a lookup table of a string of blanks for each possible time difference. Any ideas anyone ?

mcarlson

Yes, you can create a dynamic string of milestone markers with spaces representing the time between each milestone by using a formula to generate spaces based on the time interval. This approach can work in a tool like Smartsheet by calculating the number of spaces needed between each milestone marker symbol.

Here’s a general approach to constructing the milestone string without a lookup table:

1. Calculate the Weeks Between Milestones:
   • excelCopy code=[Milestone 2] - [Milestone 1]
   • excelCopy code=([Milestone 2] - [Milestone 1]) / 7
   • This gives the number of weeks (or spaces) between milestones.
2. Generate Spaces Dynamically:
   • excelCopy code=REPT(" ", ([Milestone 2] - [Milestone 1]) / 7) & "•" & REPT(" ", ([Milestone 3] - [Milestone 2]) / 7) & "•"
   • Here, REPT(" ", weeks_between) adds the calculated number of spaces between milestone symbols (•). You would add one REPT(" ", weeks_between) function for each milestone interval.
3. Build the Full Milestone String:
   • excelCopy code= "•" & REPT(" ", ([Milestone 2] - [Milestone 1]) / 7) & "•" & REPT(" ", ([Milestone 3] - [Milestone 2]) / 7) & "•"
   • Adjust the formula for the exact number of milestones you have, adding REPT segments as needed.

Example Output

If Milestone 1 is 01/01/2024, Milestone 2 is 01/15/2024, and Milestone 3 is 01/29/2024:

• ([Milestone 2] - [Milestone 1]) / 7 would give 2 spaces for the first interval.
• ([Milestone 3] - [Milestone 2]) / 7 would give another 2 spaces.

The final string would look something like: "• • •" (with spaces representing time intervals).

This approach lets you visualize multiple milestones on a single line without a lookup table. Note that Smartsheet formula capabilities may limit some more complex concatenations, so testing in another tool (e.g., Excel) may be beneficial for large-scale automation.

mcarlson

Yes, you can create a dynamic string of milestone markers with spaces representing the time between each milestone by using a formula to generate spaces based on the time interval. This approach can work in a tool like Smartsheet by calculating the number of spaces needed between each milestone marker symbol.

Here’s a general approach to constructing the milestone string without a lookup table:

1. Calculate the Weeks Between Milestones:
   • excelCopy code=[Milestone 2] - [Milestone 1]
   • excelCopy code=([Milestone 2] - [Milestone 1]) / 7
   • This gives the number of weeks (or spaces) between milestones.
2. Generate Spaces Dynamically:
   • excelCopy code=REPT(" ", ([Milestone 2] - [Milestone 1]) / 7) & "•" & REPT(" ", ([Milestone 3] - [Milestone 2]) / 7) & "•"
   • Here, REPT(" ", weeks_between) adds the calculated number of spaces between milestone symbols (•). You would add one REPT(" ", weeks_between) function for each milestone interval.
3. Build the Full Milestone String:
   • excelCopy code= "•" & REPT(" ", ([Milestone 2] - [Milestone 1]) / 7) & "•" & REPT(" ", ([Milestone 3] - [Milestone 2]) / 7) & "•"
   • Adjust the formula for the exact number of milestones you have, adding REPT segments as needed.

Example Output

If Milestone 1 is 01/01/2024, Milestone 2 is 01/15/2024, and Milestone 3 is 01/29/2024:

• ([Milestone 2] - [Milestone 1]) / 7 would give 2 spaces for the first interval.
• ([Milestone 3] - [Milestone 2]) / 7 would give another 2 spaces.

The final string would look something like: "• • •" (with spaces representing time intervals).

This approach lets you visualize multiple milestones on a single line without a lookup table. Note that Smartsheet formula capabilities may limit some more complex concatenations, so testing in another tool (e.g., Excel) may be beneficial for large-scale automation.

RayB

Hi Murphy - thanks - its the part2 of your analysis above where I'm having trouble and I can't find a REPT command in Smartsheet? Could you point me to it.