# Compare Planned Date to Actual Date and Return New Forecasted Start Date for next step

✭✭
edited 04/22/22

I am in a grid of project data with multiple date entries (planned and actual) for multiple steps in columns. I want to be able to reforecast project dates as the project proceeds when Actual date columns have been entered. Using these date COLUMNS:

PlannedStart1

PlannedEnd1 = (definition is 5 days after PS1 - in a formula on this column)

ActualStart1

ActualEnd1

PlannedStart2 = (1 day after AE1 unless AE1 is blank, then 1 day after PE1)

PlannedEnd2

ActualStart2....(etc)

I am trying to create a formula that will reforecast PlannedStart2 based on whether the ActualEndDate1 has been entered. If it has not been entered, then PlannedStart2= PlannedEnd1 +1 day. If ActualEndDate1 is not blank, then PlannedStart2=ActualEnd1 + 1 day.

Basically a columnar project plan that will reforecast planned and actual steps as items proceed through the steps groupings in the columns to then (in the last column) continually forecast the calculated "end date" for the item (row) vs. the original planned end date for the row in another column. Thank you in advance.

• ✭✭✭✭✭✭

Example sheet:

PlannedEnd1 formula:

=[PlannedStart1]@row + 5

PlannedStart2 formula:

=IF(ISBLANK([ActualEnd1]@row), [PlannedEnd1]@row + 1, [ActualEnd1]@row + 1)

PlannedEnd2 formula:

=[PlannedStart2]@row + 5

Is this what you want?

• Moderator

Hi @R12,

If for some reason it would be fundamental to keep your current Project disposition horizontally in columns rather than vertically in rows as in the example above, the following formulas in your "PlannedEnd1" and "PlannedStart2" columns should achieve what you intend. Note however that, contrary to the Predecessors functionality above, these formulas won't take into account working days when adding 1 day to the planned end :

• For Planned End1:=[Planned Start1]@row + 1
• For PlannedStart2: =IF([ActualEnd1]@row = "", [PlannedEnd1]@row + 1, [ActualEnd1]@row + 1)

I hope this can offer some clarity and insight on what you are intending to build. Please make sure to include screenshots of your project ensuring any confidential data is hidden if you'd need further advise.

Cheers!

Julio

• ✭✭
edited 04/28/22

@Julio S. , unfortunately I am bound to use columns as we have not main task and subtasks, but a single task as the row and steps across the top. There will be automation between each step across, and as one step completes (e.g., "actual" column filled in) the person responsible for the next step gets an automated email.

I have seen other formulas that are "close" and provide the greater of two values of two cells on a row in a third cell in the same row but not one that uses if NOT BLANK. Could you provide guidance on something in a row, for instance,

Col 1 = Planned DateForStep1,

Col 2 = Actual DateForStep1

Col 3 = PlannedDateForStep2 (etc.)

and give me a way to calculate

Col 3 = PlannedDateForStep1 + 5 days UNLESS ActualDateForStep1 is not blank then use ActualDateForStep1 in Col 3?

It's the "if not blank"/"if blank" thing that is confounding. Thank you. #Formulas

Example: this shows a value if the Actual Date is filled in, but shows nothing if the actual date is blank (I'd like it to also be able to show the planned date if actual is not filled in). And it may not be correct because of the use of "<" ....this was from someone else's formula - I don't care about greater than or less than, just planned plus 5 unless actual filled in:

=IF(AND(ISDATE([Step1(PLANNED FINISH)]@row), ISDATE([Step1 (ACTUAL)]@row)), IF([Step1(PLANNED FINISH)]@row < [Step1(ACTUAL)]@row, [Step1 (ACTUAL)]@row, [Step1 (ACTUAL)]@row), "")

This was the original formula from another thread that I was attempting to modify (unsuccessfully): =IF(AND(ISDATE([Construction Documents Complete Projected]@row), ISDATE([Construction Documents Complete Actual]@row)), IF([Construction Documents Complete Projected]@row < [Construction Documents Complete Actual]@row, [Construction Documents Complete Projected]@row, [Construction Documents Complete Actual]@row), "")

• ✭✭✭✭✭✭

Example sheet:

PlannedEnd1 formula:

=[PlannedStart1]@row + 5

PlannedStart2 formula:

=IF(ISBLANK([ActualEnd1]@row), [PlannedEnd1]@row + 1, [ActualEnd1]@row + 1)

PlannedEnd2 formula:

=[PlannedStart2]@row + 5

Is this what you want?

• ✭✭

Thank you @Matt C. - appreciate the help. :-)