Defining a phase based on dates
Hello - I have a typical project timeline sheet with Phases and Milestones of a project. Each has a Start Date, End Date, Predecessor and Duration.
For each project I have a budget sheet that list the various activities that will be performed and the forecasts cost. I need to be able to report forecasts cost based on a phase. As changes in timelines as fairly common ,I need to have this automatically changed in the budget sheet. I have examples of each sheet.
Below are two screenshots. The top is my timeline sheet and the bottom is my budget. I would like to label the phase of each column. For example the first column in the budget sheet is 01/01/22 (Jan 22) and would fall with in Dry Feasibility (11/01/21 to 01/09/21). I am having trouble even getting started - so any suggestions are welcome. Thank you
Answers
-
Hi @TCJ
If I'm understanding you correctly, you're looking for a formula to put into the budget sheet which brings back all of the names of the Phase/Milestones that are listed in your first sheet for that specific Month. Is that right?
If so, you could use a JOIN(COLLECT function to Join together all the Phase names from sheet 1 that have a date range that's associated with your specific month column.
For example:
=JOIN(COLLECT({Phase Column Sheet 1}, {Start Date}, <=DATE(2022, 01, 31), {End Date}, >=DATE(2022, 01, 01)), CHAR(10))
The CHAR(10) at the end is a line break so you'll want to ensure you use wrap-text on the cell that you place the formula in.
Then you would just need to swap out the date ranges you're looking for within the DATE function to search for the correct month.
See: Formula combinations for cross sheet references
If I've misunderstood your question, it would be helpful to see an updated screen capture with an example/test output to show your desired goal (where the formula should be put, what type of content to bring back, etc).
Thanks!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!