#1
|
|||
|
|||
Formula or VB to get max date with multiple dates and predecessors?
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! Last edited by mjank72; 07-02-2015 at 09:54 AM. Reason: Formatting was worse than expected & added attachment |
#2
|
||||
|
||||
Try:
Code:
=IF(ISNUMBER(D2),(IF(ISBLANK(VLOOKUP(MID(D2,MOD(MAX(SEARCH({9,8,7,6,5,4,3,2,1,0},D2&"9876543210")),10),1),$A$2:$H$10,8,FALSE)),VLOOKUP(MID(D2,MOD(MAX(SEARCH({9,8,7,6,5,4,3,2,1,0},D2&"9876543210")),10),1),$A$2:$H$10,6,FALSE),VLOOKUP($D2,$A$2:$H$10,8,FALSE))),$B2)
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
IF formula using dates | rindelsk | Excel | 1 | 04-06-2015 01:15 AM |
Multiple predecessors | mrgipponi | Project | 3 | 09-01-2014 11:38 AM |
IF Formula to calculate dates | Sophie1 | Excel | 2 | 04-23-2014 07:19 AM |
Default dates for a Date Picker | BoringDavid | Word VBA | 2 | 09-11-2013 01:42 AM |
If formula for subtracting dates | gbaker | Excel | 6 | 10-07-2012 07:06 AM |