Project template with task due dates based on final end date

EMW22

Hello,

We have a recurring event that happens 3-4x a year and all the tasks leading up to each event are the same. I'd like to create a template where I can enter the event date and it will calculate when the tasks are to be started based on a set number of days (i.e., 90 days prior to event start task xyz). I'd also like to have deadlines for each task. What would be the easiest way to do this?

This way, we're not hand calculating when tasks are to start and are due as we have been doing. We can simply enter the event date and it gives us our timelines and dates to work with. I'm very new to working with formulas and any help would be appreciated.

Thanks!

Genevieve P.

Hi @EMW22

What I would do in this instance is make sure that all of my rows in the Project sheet template are linked to each other with Predecessors.

Then I would use a three fields in the Sheet Summary area of my sheet to identify what we should adjust the Start Date of the project to be, based on the end Due Date.

So, if this is the Template with incorrect pre-set dates, I have 3 fields in the Sheet Summary to help me adjust this Project:

• The Due Date (manual entry)
• The total Days of the Project (I've used a formula but you could manually input this)
• and then the Proposed Start Date (formula)

The Working Days formula is set to look at my very top Parent Row in the sheet. Since it's a Parent of the entire project, it returns the earliest Start Date and the latest End Date in the project. This means my formula can simply be:

=NETWORKDAYS([Start Date]1, [End Date]1)

This returns how many working days are in the entire sheet. Then I can use this number and subtract it from the Due Date to find the Start Date:

=WORKDAY([Due Date]#, -[Total Task Working Days]#) + 1

This will all happen automatically, so all you have to do is throw in a Due Date in the Summary field and it will show you what you may want to set the first Start Date to be.

Since all of my tasks are linked together with Predecessors, all I have to do is make the first task in the Project the same as the Proposed Start date:

This will then update the entire project with the new timeline, based on the Due Date!

Cheers,

Genevieve

jen5280

This is helpful but I want to track multiple projects on one sheet. How can I calculate task start and end dates based on a target end date? I have set up a 'dummy' line that holds the desired end date. Shouldn't I be able to use formulas to calculate task dates?

Genevieve P.

Hi @jen5280

This may depend on how your sheet is set up. You could use the Sheet Summary fields like I've done in the example above and have 3 Fields per-project in your sheet. Then just make sure that each Project has one Parent Row that encompasses all of the Child Tasks associated with that one project.

In the NETWORKDAYS formula, you'll want to select the Start and End dates for that specific Parent row. In the example above that's row 1:

=NETWORKDAYS([Start Date]1, [End Date]1)

But say you had a second project start on row 20 with a new top-level parent row, you could adjust this:

=NETWORKDAYS([Start Date]20, [End Date]20)

Cheers,

Genevieve