Defining a phase based on dates

TCJ

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

Genevieve P.

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