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

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.

Find more posts tagged with

Accepted answers

Mike TV

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?

All comments

Julio S.

Hi @R12,

Is there a specific reason why the different stages of your project aren't deployed in rows rather than in columns? If this isn't an obstacle, what you intend to do could be benefitted from having Project functionality such as Dependencies and Baselines to automatically perform the calculations you intend. In my example below I've set the different Actual and Planned Start and Finish dates in 4 columns and each phase of the task would be a sub-task of the main task to complete that then rolls up the overall task information as a Parent roll-up. You can add lag to a predecessor to add the additional daHere you can find more details on how to add hierarchy to your tasks. Note how the predecessors automatically adds one (working) day to the end finish of sub task 1 to make it the start of sub-task 2 and how any modification on the start and finish dates for each task are automatically tracked in project baselines and the variance column:

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

R12

@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), "")

Mike TV

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?

R12

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.