gantt chart - dependencies that are not necessarily SS or FF (or any other combination)

bbelman ✭✭
edited 12/09/19 in Formulas and Functions

I have a project where Activity A is anticipated to run for a month.  And Activity B starts around the same time, maybe a little later (start date is independent of A).. however  B can NOT be closed out until A is complete.. AND B has a hard stop due date.  how do I set THAT one up?  cuz I don't want B to automatically shift out if A runs late - I want to flag it somehow that the DURATION for B is shrinking... is there a way I can lock/protect/freeze the Due Date for B?  


  • Gwyneth C
    Gwyneth C ✭✭✭✭✭✭


    Thanks for the interesting problem!

    Here’s a one way that you could flag Task B if its end date appears to be in jeopardy of moving past a specific finish date. Although the scenario you map out states that the tasks are independent, there is a dependency in that Task B can’t be closed out until Task A is complete. My suggested solution does require creating a dependency between the two tasks.

    This is a two step solution where you’ll link the two tasks and then use a formula to alert you that the second task is has moved past its hard stop and requires attention.

    Step 1: Create a Finish-to-Finish Dependency Between the two tasks.

    In the Predecessors column for Task B, link it to the row for Task A and specify “FF” to note the dependency type.



    (You can find more information about dependencies and predecessors here:

    Step 2: Create a Flag Column and Use a Formula to Alert You of the Date

    1. Right-click the Predecessors column.

    2. Click Insert Column Right.

    3. Name the column At Risk (of course, it’s your choice what you want to call this) and use the Symbol/Flag column type.

    4. In the At Risk column, type the following formula

      =IF(Finish2 > DATE(2018, 7, 4), 1, 0)


      Finish2  is a reference to the cell that contains the finish date for Task B.

      The values for DATE match the year, month, and day of the hard stop for Task B.

    In this example, the hard stop is 7/4/18. (Note that if you wanted advance notice you could, for example, make this date a week earlier than the hard stop.)

    Now, here’s where the magic happens… if I change the date of Activity A to go beyond the hard stop of 7/4/18, this impacts the Finish date of Activity B, and the formula and flag alert me to the issue.



    You could make this even more powerful by creating Conditional Formatting or Notification rules. More on those features here:

    Apply Formatting Automatically with Conditional Formatting Rules

    Notifications: Keep Track of Sheet Changes

    Maybe others here have some different ideas for how to solve this (that is, after all, the beauty of this community).

    Thanks again for the scenario!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!