Trying to get dates to populate off formulas

Jezza

Essentially, I need to have users manually enter a start date (this could be either one cell or I could dedicate a column if needed), and based on the needed duration for each tasks the start and end date columns would be generated.

For the Duration column, I would like this to populate via lookup table as the durations for the tasks change based on the study type

• If study template =A, use X * [variable #1]

• If study template =B, use X * [variable #2]

• If study template =C, use X * [variable #3]

So that the duration days are calculated by formula, which I think is impossible with a duration formatted column and because of this I am fine with just a generic column type.

All of this would be very easy to do in Excel and yet seems to be unnecessarily difficult, if not impossible, in SS.

Any help is greatly appreciated!

Genevieve P.

Hi @Jezza

You are correct that Start Date, End Date, and Duration columns cannot have formulas in them when dependencies are enabled.

You could have a regular Text/Number column and use a Change Cell workflow to add the correct duration based on the Study Template. Then if you have the Start Date, you can use the WORKDAY Function to add the number of days in your duration to the Start Date to find the End Date:

=WORKDAY([Start Date]@row, [Duration Column]@row)

However you would need all Start Dates populated for every single task for the whole sheet to auto-calculate.

I would suggest keeping the current Project Sheet set-up that you have already, ensure all tasks are dependent on each other, working their way back up to the very first task (in your case, "Protocol and CRF Writing", row 3). You would need to manually update the first task's start date after the submission comes in, but it will then update all following tasks automatically.

Please feel free to submit your feature request to the Product team by creating an Idea Post in the Smartsheet Product Feedback and Ideas topic here in the Community.

Cheers,

Genevieve

Jezza

*To clarify - they would enter a base start date (for entire Study) and all following start/end dates would calculate.

Genevieve P.

Hi @Jezza

You are correct that Start Date, End Date, and Duration columns cannot have formulas in them when dependencies are enabled.

You could have a regular Text/Number column and use a Change Cell workflow to add the correct duration based on the Study Template. Then if you have the Start Date, you can use the WORKDAY Function to add the number of days in your duration to the Start Date to find the End Date:

=WORKDAY([Start Date]@row, [Duration Column]@row)

However you would need all Start Dates populated for every single task for the whole sheet to auto-calculate.

I would suggest keeping the current Project Sheet set-up that you have already, ensure all tasks are dependent on each other, working their way back up to the very first task (in your case, "Protocol and CRF Writing", row 3). You would need to manually update the first task's start date after the submission comes in, but it will then update all following tasks automatically.

Please feel free to submit your feature request to the Product team by creating an Idea Post in the Smartsheet Product Feedback and Ideas topic here in the Community.

Cheers,

Genevieve