Calculate dates in each step in a project 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 D) and the days between (column E).
I then used the calculations in column D and E to calculate new dates (column H and I) based on a new finish date (cell I11).
Download excel *.xlsx file
Related posts:
Excel vba: Select value based on time and date
Calculate your stock portfolio performance with Net Asset Value based on units in excel
List dates outside specified date ranges in excel
Excel: Calculate last date of a specific month
How to calculate missing months in a given date range in excel
Recommended categories
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:
http://www.cpearson.com/excel/datedif.aspx
Read: Calculating age