If Then within Predecessors

Penguin

Is there a way to do an If/Then statement within a predecessor field? I don't want dates calculated on a particular row until a status is changed to "Complete".

Jason Tarpinian

Formulas are not allowed in dependency-enabled columns, like predecessors/duration/start/end. What reason do you not what the date to show up for, is there another solution you could look for?

Penguin

I am using SmartSheets for project management on larger projects & I am looking to have a task start only after another task is marked off as completed. I am currently using predecessors to have tasks start at certain times, however, if the date isn't updated right away (on the predecessor) the next task is shown as being due when in reality it isn't.

Is there a way to have a task date created once another task's status is marked as completed?

Jason Tarpinian

I've been there before! The solution I use is a helper "Status" column to show me if something is "Overdue".

=IF([% Complete]@row = 1, "Complete", IF(AND([End Date]@row < TODAY(), [% Complete]@row <> 1), "Overdue", IF([% Complete]@row > 0, "In Progress", "Not Started")))

Then you can build a report or conditional formatting for the PM team or project lead for the "Overdue" tasks, and make it a priority to either mark it as complete or change the date. This won't resolve your core issue of "this task can't actually start today because the predecessor isn't actually done", but that's what Project Manager's are for 😄

If you don't like that process, then you could build a non-dependency-enabled schedule. Depending on how complex your predecessors are, may not be possible. But for simple FS it could be something like

=IF([Status]1 = "Complete", [End Date]1 + 1, "")

That could be a formula that you would put in row 2 Start column, saying that if the Status of row 1 is "Complete", then add one day to the task 1 end date. If it's not "Complete", leave the start in row 2 blank.

Matt Lynn-PCG

@Penguin You cannot do any formulas in a dependency column, including predecessor and the date columns. In this case I have created another date column(s) and hidden the dependency date column. Have the new columns = the dependency date columns while including your if() statement. This way you can use the predecessors and dates but do what you're asking. However this prevents you from interacting with your actual dependency date columns unless you want to show them all. I have done this before and called the dependency date columns "Planned" and the non dependency "actual" or visa versa depending on your situation. There's a few work arounds but they come at a risk or a cost.