Projected and Actual Dates

[zippitydoodah]

Hello,

I need help formatting an excel spreadsheet. I am not sure if it will require a macro or a basic formula.

The spreadsheet has 4 columns: Duration (B1), Projected Start Date (C1), Projected Finish Date (D1) and Actual Finish (E1). The first three columns - duration, projected start and finish all have numbers/dates entered into them at the start of a three month period. The projected finished dates are calculated based upon the projected start date + duration, so D1 =SUM(C1+B1). The actual finish column (E1) remains blank until we reach that date in the process. What I would like to happen is once there is a date entered into the actual finish column E1, that exact date is changed and reflected in the the start date column as we can't start a new project until we finish the last.

So if a project was supposed to start 6/1/2015 (C1) and last 1 day (B1) the projected finish would be 6/2/2015 (D1). However if the project actually finished a day late 6/3/2015 (E1) I want the following start date in C2 to be affected by this and change to 6/3/2015 which would then affect the rest of the schedule.

I have tried several things and can't come up with a solution.

 

[BrianG86]

I would suggest you add a hidden column. Lets say in Column F, for each line you put in the start date you want.

Then in C2, try the formula

=if

 

[BrianG86]

Sorry, try

=IF(E1-D1>0,F2+(E1-D1),F2)