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
Dates category
This article demonstrates a formula that returns a date based on a week number and a weekday like Sun to […]
Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]
The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]
The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]
Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]
Question: Sam asks: Is there a formula that can count blocks For eg in your picture (see picture above) if […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie […]
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 […]
Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]
This article demonstrates a formula that creates date ranges based on a given number of days and the end date […]
The array formula in cell B3 extracts overlapping dates based on the date ranges in columns D and E. What's […]
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]
The formula in cell B7 and C7 extracts whole weeks within the given date range in cell B3 and C3. […]
Array formula in D2: =INDEX($A$2:$A$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)) Array formula in E2: =INDEX($B$2:$B$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, […]
The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]
This blog article describes how to extract coinciding date ranges using array formulas, the image above shows random date ranges […]
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]
The image above shows a formula in cell D3 that extract dates from column B. Column B contains dates in […]
This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]
This article explains how to create a formula that returns the earliest (smallest) and the latest (largest) date of overlapping […]
The formula in cell B8, shown above, extracts dates not included in the specified date ranges, in other words, dates […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier […]
This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range. […]
The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Question: How to calculate the date of the third Monday of a given month? Answer: Column B contains dates of […]
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
The worksheet above shows four different time ranges in column B and C, the formula in cell C10 counts the […]
This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
This article explains how to find the smallest and largest value using two conditions. In this case they are date […]
The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]
I will in this article show you how to extract the most frequent value (text or number) between two dates […]
The picture above shows an Excel Table with Data Validation applied. An error dialog box appears if a user tries […]
This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]
This article demonstrates how to calculate overlapping numerical ranges. What is interesting to know is the fact that Excel handles […]
This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
4 Responses to “Schedule project dates based on a finish date”
Leave a Reply
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.
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 evening
I 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