Looking for Suggestions/Best Practices on How to Auto-populate Project Plan Date Fields

Sandra Guzman

Hello Smartsheet Community!

I am looking for input on best practices for getting dates to auto-populate in my project plan. I have a set of 6 date columns. I already plan to use dependencies and predecessors to populate dates based on the duration that is entered for each task. What I am looking for is guidance or recommended suggestions that will help me to mimic the same functionality that is available in the templated predecessor and duration columns. I want to have more automated pre-fill functionality so that the manual effort is minimized as much as possible.

So far I have tried adding a formula in the end date column.

=[Duration]@row + ([Planned Start Date]@row - 1)

There are a couple of challenges with this.

(1) The formula yields the statement #Date Expected in all the fields that contain this formula.

(1) The user still must complete all start dates in order to have the end dates populate (not the same functionality as using dependencies and predecessors).

I have also tried rewriting our old Excel formula that we used to populate dates in Excel based on duration, but I get an #INCORRECT ARGUMENT SET.

OLD EXCEL FORMULA

=IF(ISBLANK(D12), "", WORKDAY(G22,1))

Smartsheet Formula

=IF(ISBLANK([Planned Start Date]11, " ", WORKDAY([Planned Start Date]12 + 3)))

So before I continue to work on the above two solutions, I wanted to come here to see if any of you have experience with trying to recreate some formulas that will auto-populate dates in a similar manner to mimic what happens with dependencies using predecessors and duration.

I appreciate any knowledge, ideas or suggestions that you can provide.

Thanks!

Sandra

SoS | Dan Palenchar

Are your column types all correct? E.g., duration is a text column, date fields are date columns. Your formula works fine for me.

If your project plan has set durations and the relationship between the end date of a task and the start date of the next task is defined (e.g., start the next task the day after) you can build the duration in ahead of time, populate the project start date, and autofill the whole thing.

Also, predecessor function takes into account business days by default, your method is going to use calendar days and count weekends FYI.

SoS | Dan Palenchar

Are your column types all correct? E.g., duration is a text column, date fields are date columns. Your formula works fine for me.

If your project plan has set durations and the relationship between the end date of a task and the start date of the next task is defined (e.g., start the next task the day after) you can build the duration in ahead of time, populate the project start date, and autofill the whole thing.

Also, predecessor function takes into account business days by default, your method is going to use calendar days and count weekends FYI.

Sandra Guzman

Dan, thank you so much!

I will test this out - it sounds like what I was looking for.