Calculate "available to start date" based on finish date of task predecessors

Does anyone have any suggestions for a formula or approach (without any macros) that returns the answers in column F (available to start date) given the data in columns A-E. Additional columns can be added and there would not be more than 10 predecessors to any task.

The "available to start date" for any task is defined as the day following the latest "finish date" of the predecessor tasks where the predecessors have the same project number.

So for task 26 of project Beta the predecessors are tasks 8A and 9A. 9A has the latest finish date of 1/14/16 so the "available to start date" of Task 26 is one day later or 1/15/16

If a task has no predecessor the available to start date should be either blank or zero.

Here's a screenshot of the data and it is also attached as xlsx

Any help would be greatly appreciated

Capture.PNG

Thanks Soledad - worked brilliantly!

Originally Posted by soledad

You try this for cell F2 then copy down

Formula:

Please Login or Register  to view this content.

Note: Confirm it with CTRL+SHIFT+ENTER

Sorry to have not asked this correctly in the first place, but how would the formula change if it had to allow for task names that are numbers or strings with an indeterminate number of spaces such as "Task 5A special","3b LTD","4B","4 B", "7"

Thanks in advance

Originally Posted by InnoGuide

Sorry to have not asked this correctly in the first place, but how would the formula change if it had to allow for task names that are numbers or strings with an indeterminate number of spaces such as "Task 5A special","3b LTD","4B","4 B", "7"

Thanks in advance

Hi InnoGuide
if you have the tasks as "Task 5A special", "3b LTD", "4B", "4 B", "7" or etc...shall not affect the application of the above formula. You only need to pay attention to the following:
-. The tasks in the "predecessors" column must be separated by at least one space and terminated by one comma "," right after it with no spaces, eg Task 5A special, 3b LTD, 4B, 4 B, 7
- Each task in the "predecessors" must be exactly as it is seen in the "Task Name" column
Hope you understand

Thanks Soledad - I've got a large data set and it looks like there may be a few that violate this - Thanks for your help and clarification

Originally Posted by InnoGuide

Thanks Soledad - I've got a large data set and it looks like there may be a few that violate this - Thanks for your help and clarification

Hi Soledad - the problem ended up being that there were blank rows at the bottom of the data set that could not be eliminated.

Just used Cell X1=SUMPRODUCT(MAX((A$2:A$13<>"")*ROW(A$1:A$13))) to find the last row and then used the indirect function. So your formula became:

Formula:

Please Login or Register  to view this content.

Thanks again for all your help