Project Phase Gaps and Overlaps

TCJ

Hello, I have seen similar questions posted elsewhere, but I can't seem to make this work for my situation. I need to flag any project phases that overlap with other phases and any gaps of more than Xdays between the end of one phase and the start of another phase.

In the project plan, there are official phases (marked by a check in the Phase (y/n) column. There will be rows that are not Phases or Milestones (they will be project specific activities). The highlighted Start Dates have overlaps with the previous phase.

Spoonhead

To flag overlapping phases, use the following formula in a "Flag" column (let's call it OverlapFlag):

=IF(AND([Phase (Y/N)]@row = 1, [Phase (Y/N)]@row - 1 = 1, [End Date]@row - 1 >= [Start Date]@row), 1, 0)

This formula checks if the current row and the previous row are phases and if the end date of the previous phase is greater than or equal to the start date of the current phase. If so, it flags the overlap.

To flag gaps of more than X days (e.g., 5 days) between phases, use the following formula in a "Gap" column (let's call it GapFlag):

=IF(AND([Phase (Y/N)]@row = 1, [Phase (Y/N)]@row - 1 = 1, [Start Date]@row - [End Date]@row - 1 > X), 1, 0)

Replace X with the number of days you want to consider as a significant gap. This formula checks if the current row and the previous row are phases and if the gap between the end date of the previous phase and the start date of the current phase is greater than X days. If so, it flags the gap.

You can then use Conditional Formatting to highlight the rows where OverlapFlag or GapFlag equals 1.

1. Go to Conditional Formatting.
2. Set a new rule where the condition is that the OverlapFlag or GapFlag column equals 1.
3. Choose a colour to highlight these rows.

Hopefully something like that could work?

TCJ

Thank you. This solution will only work if the phases are in sequential rows, and they rarely are.