How to take Not Applicable and Completed item in consideration when calculating schedules?


Hi, I am working on building construction scheduling. In the template of my schedule, I have a list of items with predecessors. My questions, once I marked one item as N/A or DONE(two separate check boxes), how can I make the following items that have relation to this item to catch up? For example. let's say item A was on schedule for 6/1, and it's successor item B was on schedule for 6/2. If item A marked as N/A, then automatically, item B move up to 6/1. Also, if item A original finished date was 6/5, but when I marked it DONE on 6/4, it will change the completion date for item A and move up the item B's starting date.

I tried to use some formulas, but the predecessors, Start date, and Finished date are restricted and won't take any formula.

Any suggestions will be very grateful.



  • FreezeBrew

    Anyone can help?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @FreezeBrew

    As you've found, project setting columns are unable to host formulas to automatically make changes like this based on the Status.

    In order to have a Start Date of a lower task automatically update, you would want to have it be Dependent on that previous task (which it looks like you've already set up!). Then, when the task is either marked N/A, or DONE, you will also want to adjust the End Date to reflect the new, actual End Date. This will then automatically adjust the next task because the next task is dependent on the finish date. Does that make sense? (See here for more information on dependencies.)

    If you want to track the proposed Finish date along with the Actual Finish Date, you could add in a separate, helper column that shows what was originally supposed to be the Finish. Then use the Actual Finish Date column for the dependencies so that your tasks adjust when you change the date.

    Hope this helps!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!