Dynamic Date Calculation

PaytonG

Good evening!

I've tried everything and researched the forums but no luck. I was hoping someone could help out. I am trying to utilize Smartsheet as a scheduling tool, however we have jobs that are shifted around constantly so having the ability to drag rows around and recalculate dates would be extremely useful.

Starting out with the basics, I have a job start date ("Start" column) and job end date ("End" column) where the "End" date is the "Start" date plus some duration. The next job "Start" date is then hopefully calculated by the "End" date of the previous job. However, as you know if you reference the "End" date cell of a previous job but end up moving the job around, the "End" date stays fixed to the original cell it was calculated off.

How can I make the "Start" date dynamic so that if a row is moved around it then references to the job that is now above it?

I've got the helper rows created that re-calculate the Row number when rows are shifted around and tried Index: "Start"=INDEX(End:End, Row@row - 1) where "Row" is the column that recalculates which row number it is when something is shifted up or down, but run into the circular reference error as End is calculated off of the Start Date plus the duration and the following "Start" date is calculated off of the "End" Date.

It seems like this should be simple but I'm driving myself crazy. Any help would be greatly appreciated.

Itai Perez

Hey @PaytonG

Are you using the Smartsheet Project Managemet tool? If you right click any column and choose Edit Project Settings and allow the correct Start and End columns then any change or move of rows you make will automatically change the dates.

Also you will be able to add the duration and Predecessor columns.

https://help.smartsheet.com/learning-track/level-3-advanced-users/project-management-tutorial#:~:text=The%20length%20of%20each%20working,place%20the%20next%20working%20day.

Let me know if that helps.

PaytonG

Hey @Itai thanks for the info.

If I understand the information in that article, you still have to select a specific predecessor? Let's say a predecessor then moves behind a job that was its successor, the predecessor date would still be before the successor?

Is there a way to have the predecessor reference the relative row above it and always stays at the @row-1?

Itai Perez

Hey @PaytonG

Can you add some screeshots? I think it might help me understand the issue.

Thanks

PaytonG

Sure @Itai , that would be easier!

For example, let's say I have the below project list:

However then we shift the Job in row 2 to row 4.

The start date of the job that was originally in row 2 stays the same, as it is still tied to the specific predecessor. I would like to have it redetermine the start date by looking at the end date at the row above.

Paul Newcome

I would suggest putting the very first start date in a sheet summary field (to allow for the application of column formulas. Then the start date column would be the [Start Date]# sheet summary field plus the SUMIFS of the duration column for the rows that come before it plus 1. Then end date would then simply be Start Date plus Duration.

Start Dates:

=[Start Date]# + SUMIFS(Duration:Duration, Row:Row, @cell< Row@row) + 1

End Dates:

=[Start Date]@row + Duration

Itai Perez

Hey @PaytonG

You would have to delete or change the predecessors for the dates to change.

If you are changing the rows frequently, why not remove the predecessors altogether?

Paul Newcome

@Itai That's the whole point. They started out without predecessors for that very reason and were trying to figure out a way to make it work.