Date Formulas Calculating Back From Deadline with Gantt Functionality

Jason.Valleau

Hello Team Spreadsheet,

 

Ultimately we'd like our company to enter a date that they would like to have their marketing materials and have smartsheet automatically calculate key dates prior to this date including the initial submission date which should be around 5 weeks prior to the end date. 

 

Here is our marketing team's scenario and what function we'd like smartsheet to perform. Each of these bullet points is a separate column:

 

1. • Marketing materials in hand: End Date (for gantt functionality) This is the date (and the only date) we want our users to enter. All the other following columns we want to populate automatically.
2. • In production: One week prior to above date (using cell# minus 7 days formula) working
3. • Review: One week prior to above date (using cell# minus 7 days formula) working
4. • Design Date: One week prior to above date (using cell# minus 7 days formula) working
5. • Content Due To Designer: Two weeks prior to above date (using cell# minus 14 days formula) working
6. • Submit to marketing: this is where it gets confusing. This cell automatically displays the End Date (Marketing materials in hand) I'm using this as the Start Date column for dependency/gantt , functionality. I want this to automatically display a date 5 weeks before the end date column.
7. • Duration: only displays 1 day when working backwards from End Date (Marketing Materials in hand) Column.

 

What am I missing here? I can't seem to be able to put a formula in the Start Date column (Submit to marketing) or the duration column. Is there an easier formula I can use for this?

Peter Kirkham

Forget the Start Dates and End Dates. You don't want formulae in there. You need to focus on two things for all activities in your schedule.

 

1) Activity durations

2) Predecessors

 

For the predecessors you link all activities together working backwards, and using a SF condition e.g. start-finish. So if you have activity 1 as the target completion milestone date, and activity 2 has to finish 14 days before this milestone, then set the predecessor for activity 2 to 1SF-14. This would set the start and end dates for activity 2 automatically so that the activity finishes 14 days before the activity 1 milestone. If you change the end date for activity 1, then activity 2 dates automatically recalculate. I suspect when you are entering 'cell# minus 7 days' predecessors you are automatically defaulting to FS relationships, and this is why you can only enter 1 day durations instead of the actual length of duration required.

 

Generally you should only set one absolute hard date in your schedule. For a forward schedule, this is the start date. In your case it is the end schedule. Then you use networking (the predecessors and durations) to set every other schedule date.

 

There are other relationships such as start-start (SS) and finish-finish (FF) which you should experiment with. Experimentation is the best way to learn how they work.

 

Hope I've managed to shed some light here.

Jason.Valleau

Hey Pete, thanks for the response. 

That's a great solution however is there a way to do this on one row? We want users to be able to plug in one date for one piece of marketing task and have the dates calculated back on one line. It works fine with the formulas I've created except for as far back as the start date. When I manually change that or the duration they all go haywire.

 

Peter Kirkham

I just tried to re-create your example and I see the problem you are having. I'm not sure that it is possible to do it on one row. As far as I know Smartsheet will explicitly prevent you from putting formulae into the start, end and duration columns. I just tried creating all the columns you described with working formulae and then assigning the project start and end columns, to try and trick it, but it doesn't work. However...

 

You can create a second sheet with cell links for the start and duration to calculated start date and duration from your single row input sheet. This then allows you to do the Gantt chart, but have user input on a single row. OK, so now you've got two sheets, but it's still a sort-of single row solution. I tested this out and it works.

Jason.Valleau

Thanks for the response Peter. I found a work around. I put 35d for every cell in the duration column (this is approx. 6 weeks total) and locked the column. Now when someone enters their end date it calculates back based on the formulas and goes right back to the start date. It's a bit finicky if you try to change any of the preset dates and the duration but at least out of the gates a user only has to put in the end date and it works. I locked all of the columns so this can't be changed by mistake.

 

Thanks so much for looking into this.