Master Out of Office Calendar Sync with Individual Project Gantts?

BrittMarie

Hi! I manage several different projects with separate gantt charts, all with different project teams, and I struggle with cross-checking everyone's ooo schedules when I'm updating timelines. I would ultimately like to find a way to have a Master OOO Calendar that be pulled into the gantt charts based on the people assigned to the project so that I can see their ooo dates in each gantt without having them add their ooo dates to each and every project they're assigned to separately.

I have created a sheet for the Master OOO Calendar that contains the following columns: Name, OOO Start Date, OOO End Date

The project assignments are contained in a sheet called Active Projects.

What I'm thinking of doing is being able to include a formula at the top of each project's gantt to pull in the OOO Start & End Dates if the person is assigned to that project in the Active Projects sheet. So essentially the dates are only showing up if the person who is assigned to the project has added dates to the Master OOO Calendar.

Is this possible? Is there a different solution that would allow me to more easily adjust timelines in each project's gantt while seeing when my teams are ooo?

Thanks!

jmyzk_cloudsmart_jp

Hi @BrittMarie

Here is a demo solution.

The formula to get OOO Start and End is as follows;

=INDEX({Master OOO Calendar Range : Start}, MATCH([Assigned To]@row, {Master OOO Calendar Range : Contact}, 0))

=INDEX({Master OOO Calendar Range : End}, MATCH([Assigned To]@row, {Master OOO Calendar Range : Contact}, 0))

I provided two tools to check whether the start and finish date of an "Assigned to" person is appropriate or to determine who to assign.

OOO Gant

Under the OOO parent row, we can populate "Assigned to " persons with this formula.

=IFERROR(INDEX(DISTINCT(CHILDREN([Assigned To]$1)), [Task Name]@row), "")

This formula looks at the children of [Assinged To]$1 and gets the district person as a range, and the INDEX gets the person #1 to #5.

Then, I copied the OOO Start and End dates to the OOO Start and Finish dates. (Please note that as we want to enable dependency in the project setting, we cannot use formulas in the Start and Finish Date columns. So, I copied the OOO Start and Finish auto-populated with the formula to the Start and Finish Date to create OOO Gantt.)

OOO checkbox

The OOO checkbox checks that the Start and Finish date does not include the OOO dates defined by OOO Start and End with this formula.

=IF(PARENT([Task Name]@row) <> "OOO", IF(OR(AND(Start@row >= [OOO Start]@row, Start@row <= [OOO End]@row), AND(Finish@row >= [OOO Start]@row, Finish@row <= [OOO End]@row)), 1, 0))

With those two tools, you can check if the assignments or dates are appropriate by looking at the OOO checkbox and can adjust them by comparing them with the OOO Gantt chart.

You can access the above demo dashboard from the link below.

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