## Schedule project dates based on a finish date

I have a schedule that I am working with and based on one date (ie. 6/4/12) different processes take different times to complete (ie. one step could only take a week, another could take up to 4 weeks).

Is there a formula I can use to calculate each step in the process based off of the date range of completion for the first step in the process?

So for example, if you look at the BBD date at the bottom, all of the steps above it take a certain amount of time to complete and have to be finished on time in order for the project to be complete by 1/4/2013.

Instead of typing in manually the date ranges I am trying to write a formula that will allow me to input the project date (ie 1/4/2013) and have all of the other steps populate themselves based on how long they take to complete (ie. the manuscript to CE step could take 2 weeks, the manuscript from CE could take 1 week and so on). I hope that makes sense??

ie. ONE ROUND From: To:

Manuscript turnover 6/25/2012 7/30/2012

Manuscript to CE 8/6/2012

Manuscript from CE 8/20/2012

Manuscript to author 8/27/2012

Manuscript from author 9/10/2012

Ms to comp 9/3/2012 9/17/2012

Pages from comp 10/8/2012

Pages from author 10/22/2012

Pages to proofreader 10/29/2012

Pages from proofreader 11/12/2012

Pages to comp 11/19/2012

Confirming proofs 11/26/2012

Ship to printer 12/3/2012

BBD 1/4/2013

**Answer:**

I calculated the duration for each step (column E) and the days between each step (column F). I then used the calculations in column E and F to calculate new dates in cell range E15:D23 based on a new finish date in cell D24.

The following formula calculates the number of days between From: and To: dates.

Formula in cell E3:

The IF function checks if cell C3 is blank and then returns 0 (zero) if TRUE and D3-C3 if FALSE. Copy cell E3 and paste to cell range E4:E11.

The formula below calculates the number of days between tasks.

Formula in cell F3:

Copy cell F3 and paste to cell range F4:F11.

### Calculate new dates based on finish date

The following formula in cell C15 checks if C3 is blank and returns a blank if TRUE or returns D15-E3 if FALSE. D15-E3 calculates the **From:** date by subtracting the duration from the** To:** date.

Formula in cell C15:

Copy cell C15 and paste to cell range C16:C23.

The formula below calculates the **To: **date by subtracting the number of days between the tasks from the **To**: date of the next task.

Formula in cell D15:

Copy cell D15 and paste to cell range D16:D23.

The formulas above make it possible to change the date in cell D24 and the other project dates will follow based on the calculations made in the first table.

This Gantt chart shows the processes and the days between, green days are the task duration and red days are the number of days between the current task and the next task.

Read more about Gantt charts here: Dynamic Gantt charts

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

Find latest date based on a condition

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

Formula for matching a date within a date range

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

### 4 Responses to “Schedule project dates based on a finish date”

### Leave a Reply to Martins

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Oscar,

This is brilliant!!! I love how this is set up because if the duration of one step changes I can update it and then my schedule will automatically change too! Thanks for your help! I'll def remember you in the future if I need help!

Danielle

Danielle,

you are most welcome! Thanks for commenting!

Ola boa noite

preciso de sua ajuda no seguinte

tenho na celula A1 a data de 23/01/2009

tenho na celula A2 a data de 28/06/2011

preciso de uma formula que me de como resultado os dias meses e anos que separam as duas datas ou seja como resultado quero 5/05/02

è que se fizer A2-A1 da-me como resultado mais um mes 5/06/02

Martins,

Hello good eveningI need your help in the following

I am in cell A1 to date 23/01/2009

I'm in cell A2 of the date 28/06/2011

I need a formula I as a result of the days, months and years between two dates or as a result I 05.05.02

è is made A1-A2 of me as a result over one month 06/05/02

I think this webpage answers your question:

https://www.cpearson.com/excel/datedif.aspx

Read: Calculating age