How do I create a formula when the complete date is entered, it will trigger the next start date?

Katie Aldrich

I am trying to figure out a formula that when I actually complete something, it will trigger the next start date task.

Example:

I have start date, due date and complete date.

Start date would be 2/1/21, with a due date of 2/10/21.

I'm then filling in when the task is actually completed, 2/15/21, I would like the next start date to reflect a new date to start of 2/16/21. (instead of 2/11/21).

The above formula did not help me as I don't want to add days.

Thank you this would help a lot if there is a simple formula!

Genevieve P.

Hi @Katie Aldrich

If all your columns are Date Type of columns, you can reference a date cell and add 1 (for the next day).

So if the current row is dependent on Row 3's Complete date, you would put this formula into the Start Date for the current row:

=[Complete Date]3 + 1

Then the new Start Date will automatically populate as soon as the referenced date is updated or added. See: Use Formulas to Perform Calculations With Dates

If you'd like to display the Due Date + 1 as a "potential" start date until there's a Completed date, you could do the following:

=IF([Complete Date]3 = "", [Due Date]3 + 1, [Complete Date]3 + 1)

This says, if the Completed Date for row 3 is blank, return the day after the Due Date for row 3. Otherwise, return the day after the Complete Date.

I will note that instead of building formulas, it may be easier to use the Predecessor / Dependency function within Project sheets to automatically have rows dependent on each other. See: Enable Dependencies and Use Predecessors

Let me know if I can clarify anything further or if this makes sense!

Cheers,

Genevieve

Katie Aldrich

Hello,

Thank you, I do have a Smartsheet that is utilizing predecessors. I would like a version that utilizes the completed date to start the next task to account for delays in tasks.

All columns I am pulling from are date columns. When I tried to enter the formula, the predecessor disappeared row 9 and the formula does not compute.

The formula isn't working above in my spreadsheet for some reason. It is in column task start date row 9.

Genevieve P.

Hi @Katie Aldrich

Thank you for clarifying!

You won't be able to use date columns that are set in your Project Settings for both formulas and as a column with dependencies/predecessors. You would have to decide either-or.

See the note at the top of this page: Project Sheet Columns: Start Date, End Date, Duration, % Complete and Predecessors

I would suggest implementing Baselines instead to track your original Start and End dates, then you can update the actual End date (instead of a Completed date column) which would update any dependent tasks. I hope that helps!

Cheers,

Genevieve

Katie Aldrich

Hi @Genevieve P

The baselines would work but if we update to the actual end date, the predecessors disappear for that task. I would like to keep both options so to speak in my sheet.

Is this possible to create a formula that would change the start date based on the previous task's complete date or do I need to delete the predecessor column first?

Thank you!

Katie Aldrich

I have also tried this and the formula is still not working.

Genevieve P.

https://community.smartsheet.com/discussion/comment/298370#Comment_298370

Hi @Katie Aldrich

I'm not quite sure I understand what you are describing in the first sentence, would you mind elaborating further? If you updated the actual end date to be a different day than what was initially set, and if this "Actual End Date" column is your End date in Project Settings, then this will keep your predecessors. Updating the End Date will adjust all the following dates to reflect their new, updated start dates (since they would be dependent on the end date which moved). It would do the work of the formula for you.

To set this up as a formula instead, then yes, you would need to disable dependencies and ensure that this column is not used in Resource Management, either. (See: Areas where formula use is restricted). Is it possible that this sheet has Resource Management enabled?

Katie Aldrich

Hi @Genevieve P

I am confused, I thought I had to remove the durations and the predecessor columns to make the date formulas work?

None of the date formulas are calculating and I cannot figure it out.

Genevieve P.

Hi @Katie Aldrich

Is it possible these columns are used in Resource Management, as well?

To check this, change your sheet to Gantt view, click on the gear icon in the top right corner (under the "Share" button), then check the Resource Management tab on the left.

Date columns that are used in Resource Management cannot contain formulas. The first tab (Dependency Settings) will identify what columns are used in your Project Settings. See: Areas where formula use is restricted.

Let me know if this is the issue!

Cheers,

Genevieve

Katie Aldrich

Unfortunately no that was not the issue.

I am not sure why the formulas are not working. Please let me know if you can think of anything else. Thank you.

Genevieve P.

Hi @Katie Aldrich

Try adding in an entirely fresh Date Type of column - does the formula work there? Then open the Project Settings and adjust your Gantt chart to look at this new column instead.

Make sure that this box is un-checked, as well: