Project Milestones on a single line as a string?
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 ?
Best Answer
-
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:
- 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.
- 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 oneREPT(" ", weeks_between)
function for each milestone interval.
- 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, andMilestone 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.
Murphy Carlson
DigitalRadius, Smartsheet Platinum Partner
mcarlson@digitalradius.com
- Calculate the Weeks Between Milestones:
Answers
-
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:
- 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.
- 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 oneREPT(" ", weeks_between)
function for each milestone interval.
- 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, andMilestone 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.
Murphy Carlson
DigitalRadius, Smartsheet Platinum Partner
mcarlson@digitalradius.com
- Calculate the Weeks Between Milestones:
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives