I'm using Excel to track an effort and have most of the formulas down, but am struggling finding the right formula to populate a date field for a task based on the maximum of either the actual stop date or planned stop date of it's predecessors. I can get it to work if there is a single predecessor and I can get it to work for multiple predecessors if there's only one date column to pull from. Here is are some examples.

Example: single predecessor

Step # Baseline Start Date Duration (min) Predecessor Adjusted Start Date Adjusted Stop Date Actual Start Actual Stop
1 7/17/15 23:00 0 7/17/15 23:00 7/17/15 23:00 7/17/2015 23:00 7/18/2015 12:00
2 6/26/15 12:00 30 1 7/18/15 12:00 7/18/15 12:30 6/26/15 13:00 6/26/15 14:00
3 6/26/15 12:00 2880 1 7/18/15 12:00 7/20/15 12:00 6/27/15 15:00 6/30/15 12:00
4 6/26/15 14:59 721 1 7/18/15 12:00 7/19/15 0:01
5 7/3/15 12:00 15 1 7/18/15 12:00 7/18/15 12:15
6 6/26/15 14:00 700 2 6/26/15 14:00 6/27/15 1:40
7 6/26/15 12:00 2 3 6/30/15 12:00 6/30/15 12:02
8 6/26/15 12:00 631 6 6/27/15 1:40 6/27/15 12:11
9 6/26/15 12:00 2440 7,8 6/26/15 12:00 6/28/15 4:40

E#= {=IF(ISNUMBER($D2),(IF(ISBLANK(VLOOKUP($D2,$A$2:$H $10,8,FALSE)),VLOOKUP($D2,$A$2:$H$10,6,FALSE),VLOO KUP($D2,$A$2:$H$10,8,FALSE))),$B2)}
Again, this formula works with a single predecessor, but once you enter multiples it falls back to the "Baseline Start Date."

Example: multiple predecessors

Step # Baseline Start Date Duration (min) Predecessor Adjusted Start Date Adjusted Stop Date Actual Start Actual Stop
1 7/17/15 23:00 0 7/17/15 23:00 7/17/15 23:00 7/17/2015 23:00 7/18/2015 12:00
2 6/26/15 12:00 30 1 7/18/15 12:00 7/18/15 12:30 6/26/15 13:00 6/26/15 14:00
3 6/26/15 12:00 2880 1 7/18/15 12:00 7/20/15 12:00 6/27/15 15:00 6/30/15 12:00
4 6/26/15 14:59 721 1 7/18/15 12:00 7/19/15 0:01
5 7/3/15 12:00 15 2 6/26/15 14:00 6/26/15 14:15
6 6/26/15 14:00 700 3 6/30/15 12:00 6/30/15 23:40
7 6/26/15 12:00 2 4 1/0/00 0:00 1/0/00 0:02
8 6/26/15 12:00 631 1,2,6 7/18/15 12:00 7/18/15 22:31
9 6/26/15 12:00 2440 2,3 6/30/15 12:00 7/2/15 4:40

E#= {=IF($D22="",$B22,MAX(IF(ISNUMBER(FIND(","&$A$21:$ A$30&",",","&$D22&",")),$H$21:$H$30)))}

This formula works if I only have it pull from the "Actual Stop Date" column. If I modify it to look at the "Adjusted Stop Date" column (if Actual Stop is empty) I get a circular reference warning and it fails.

Basically, I'd like to have the "Adjusted Start Date" update based on the latest of it's predecessors "Actual Stop Date" (if exists) or "Adjusted Stop Date". Any help is appreciated and I'm not against creating a VB module, but I don't know that language.

Thanks!

Attached Files

TAP_Examples_v1.xlsx (13.8 KB, 12 views)

Try:

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]