Project Delays and Dependencies

anesbeda
anesbeda
edited 03/22/24 in Formulas and Functions

I have created a project timeline that includes dependencies. If there is a delay with Item A, I want this to automatically adjust the timeline/dates for the rest of the project. For example, Item A is completed 3 days late. I then want the start and end dates of Item B / C / D etc to shift forward 3 days. What is the best way to address this in Smartsheet? Also, what is the best way to mark these items as delayed in Smartsheet and to alert others that their deadlines may have changed due to an earlier delay in the project? Thank you!

Answers

  • brianschmidt
    brianschmidt ✭✭✭✭✭

    I think you're on the right track and a big part of this is making sure that your dependencies are written appropriately for what you need to ensure dates slide when others shift. For our project schedules, we also have a "Status Health" and a percent complete column. In the status health column, here's a formula I have (considers also different types of row labeled from a "Row Type" dropdown column):

    =IF(OR([Row Type]@row = "Summary", [Row Type]@row = "X", [Row Type]@row = "Header", [Row Type]@row = "Project", AND([Row Type]@row = "Change Order", Start@row = "")), "", IF(AND([Row Type]@row = "", [Percentage Complete]@row = 1), "Green", IF(AND([Row Type]@row = "", Finish@row < TODAY(), [Percentage Complete]@row <> 1), "Red", IF(AND([Row Type]@row = "", Finish@row <= TODAY() + 7), "Yellow", IF(AND([Row Type]@row = "Change Order", [Percentage Complete]@row = 1, Finish@row <> ""), "Green", IF(AND([Row Type]@row = "Change Order", Finish@row < TODAY(), [Percentage Complete]@row <> 1, Finish@row <> ""), "Red", IF(AND([Row Type]@row = "Change Order", Finish@row <= TODAY() + 7, Finish@row <> ""), "Yellow", IF(AND([Row Type]@row = "Engineering", [Percentage Complete]@row = 1), "Green", IF(AND([Row Type]@row = "Engineering", Finish@row < TODAY(), [Percentage Complete]@row <> 1), "Red", IF(AND([Row Type]@row = "Engineering", Finish@row <= TODAY() + 7), "Yellow", IF(AND([Row Type]@row = "Procurement", [Percentage Complete]@row = 1), "Green", IF(AND([Row Type]@row = "Procurement", Start@row < TODAY(), [Percentage Complete]@row <> 1), "Red", IF(AND([Row Type]@row = "Procurement", Start@row <= TODAY() + 7), "Yellow", IF(AND([Row Type]@row = "Procurement*", [Percentage Complete]@row = 1), "Green", IF(AND([Row Type]@row = "Procurement*", Finish@row < TODAY(), [Percentage Complete]@row <> 1), "Red", IF(AND([Row Type]@row = "Procurement*", Finish@row <= TODAY() + 7), "Yellow", IF(AND([Row Type]@row = "Permitting", [Percentage Complete]@row = 1), "Green", IF(AND([Row Type]@row = "Permitting", OR(AND(Start@row < TODAY(), [Percentage Complete]@row < 0.01), Finish@row < TODAY()), [Percentage Complete]@row <> 1), "Red", IF(AND([Row Type]@row = "Permitting", OR(AND(Start@row <= TODAY() + 7, [Percentage Complete]@row < 0.01), Finish@row <= TODAY() + 7)), "Yellow", IF(AND([Row Type]@row = "NoMP", [Percentage Complete]@row = 1), "Green", IF(AND([Row Type]@row = "NoMP", Start@row < TODAY(), [Percentage Complete]@row <> 1), "Red", IF(AND([Row Type]@row = "NoMP", Start@row <= TODAY() + 7), "Yellow", "Blue"))))))))))))))))))))))

    This is lengthier a set of statements than you may need, but gives you an idea of how you could leverage this column. We use these status health bubbles to then send automated update requests, notifications, and the like for items not marked as complete with certain status health). Often, if a deadline is going to be pushed, it's just then important that estimated "Finish" dates are shifted appropriately.

    It's hard to say what else would be helpful without fully understanding your use case. However, you could also have a contact column with "Assignees" and set up automations that inform them if anything (or just dates) on their assigned row(s) change. That way, they are informed of how the shifts on other rows affect them.

    Hope this helps!:)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!