Internet Computer Mobile Games

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!

Attached Files
TAP_Examples_v1.xlsx (13.8 KB)

Top similar posts to Formula or VB to get max date with multiple dates and predecessors?

Formula to calculate multiple dates to return two results

Hello! Can you please help me? I have all of the details attached in the spreadsheet. Thanks in anticipation :-) Attached Files Book1 - Test.xlsx‎ (10.4 KB) Download ...

Need a formula to sum for a date with multiple rows into a single row

Hello, I attached a workbook for reference. There's more than 1 row for each date of the year. I need the sum of L_ULID for each date in the second table. I think i should be using sumproduct, but can't get it to work properly. Data2.xlsx ...

Formula to find a date within a range of dates for specific rows

Hi guys, I'm really struggling to write one formula, I'll be really glad someone could help me with this one. conditions: For each user (B1:D1) / column in Table A, find matching rows in table B (A:A) and Check if date (A:A) in Table A is within a range (>=B and =<C) in Table B (for that user) I would like to use this formula in conditional formatting to highlight cells that match this rule. Basically would like to highlight cells where particular user was away from work based on data ...

[SOLVED] Formula needed for future date using variable text from 1 column and dates from another.

Hi folks, I currently have a formula in column I... =IF(H2="","",IF(H2="N/A","N/A",DATE(YEAR(H2),MONTH(H2),DAY(H2)+7))) ...which works great, however I now need to vary +7 to +14 depending on what I have in column B, whilst still maintaining the reference to the date in column H. I'm a complete novice and can't figure out how the formula should be written to add +7days from the date in column H if column B reads Priority and +14 from the date in c...

Formula that counts dates that are between 60 days and 30 days before todays date

Hi guys, I have a load of dates in column k and i am trying to come up with a formula in one cell which allows me to count the number of dates that are between 30 and 60 days before todays date then in another cell using the same range count the number of dates that are between 90 and 120 days before todays date? Appreciate any help on the formula, Thanks. ...

Formula for determining a date is between two sets of dates

Hello, I have a program that tracks my Employees upcoming business trips. I have a field that determines if today or a future date falls between a set of dates. That works fine. But now I need to determine if the future date falls between one of two sets of future dates. If the future date falls between either two sets of dates , then return yes, of not NO. Example. Future date 15 Feb (Field A1) First set of dates 1 Jan 15 - 30 Jan 15 (B1 and B2) Second set of dates 1 Mar 15 - 30 ...

[SOLVED] Need Formula that will multiple 2 cell if the date fall within a specified range

Need Help - Need Formula that will multiple 2 cell if the date fall within a specified range Attached Files Need Formula.xlsx‎ (9.4 KB) Download ...

[SOLVED] Date format for two dates in a concatenated formula text included in the formula?

All, I'm trying to automate some back end data for monthly reports. Ideally, the cell would read, "09/01/2014 through 09/30/2014". I'm pulling the dates off of a report and only have 41883 and 41912 to work with. I've tried using the datevalue and just using +cell. In both instances I get "41883 through 41912". So, I'm at a loss how to convert the number format over to a date in the formula. Big time thanks in advance!! I've tried the following both ways and still get the...

Using Min and Max with Multiple if Criteria - Formula

Hi I am trying to get the attached to work and keep hitting a brick wall. What I would like the spreadsheet to do is to: A - look at the project Id in column A B - look at the codes in column B and match them to the value in row 1 of the column C - look at the dates in colmn C and return the min and max dates eg. the min dates for all [Project A] + [Code = 1] The min formula I am using is this one - I have tried it as both as is and as an array - no luck MIN(IF($A$3:$A$110=$A3,IF($B$3:$...

Find a date with multiple criteria without using an array formula

Hi everybody, great mind-gathering factory here !!!. Avoiding array formulas 'cause they're slow, found a fantastic solution by Ron Coderre at: http://www.excelforum.com/excel-gene...y-formula.html The formula was: =MIN(INDEX(((A2:A20<>F1)+(B2:B20<>G1))*10^99+C2:C20,0)) I'd like something like that, but with a MAX, not a MIN. A date must be returned (the max-last-newest in a column, fulfilling criteria), as the above example, but with a MAX, not a MIN. Thank you. ...

Convert text in multiple columns to real date and time

I have the following columns and data in Excel 2010. Here are the first few of the 3000+ rows: Code: Month        Day        Year        Time Aug        1        2014        0:30:39 Aug        1        2014        1:00:36 Aug        1        2014        1...

Find and fill multiple cells. Need formula/script

Hi I am after some help on an excel sheet for which I hope someone can show me how to do it. So here goes. In column A I have around 1000 unique cells, I need to find the same values for each in column B and then fill the cell. Column B will have multiples of the same value so each value will need to be filled. I was going to start doing this manually but then realised it would take me forever so am hoping somebody can show me the way. Look forward to an answer. ...

If Formula with Multiple Options

I am trying to make a formula from 1 field to another with several options. I have a field that calculates others determining credits as follows 9th 0-5 10th 6-11 11th 12-17 12th 18+ I have tried several if(or formulas and just when I think I have it right, it errors. This is what I have tried =if(or(C6<=5,"9th",if(or(C6>6<12, "10th"),if(or(C6>12<18,"11th",if(or (C6>=18,"12th")))) I have also tried Nesting the 9th, 10th, etc =if(...

Index Match Formula needed for Multiple Criteria & Age Bands ---HELP!

I am sure there is a formula to do what I need, but I can't figure it out and I'm hoping someone can help me! In summary, I need to find (and return) the Premium Amount for an employee based on their Age and Coverage Amount. Premium Amounts are contained in another spreadsheet by "Age Bands" (in Columns) and "Coverage Amounts" (in Rows) with the corresponding premium in the applicable row/column. Since the employee's Age is their actual age (and not an "Age" Ban...

Need help with a formula with multiple variables.

Hi. I'm brand new to this forum, so I hope I am using it correctly. I am trying, and failing, to write a formula that does the following: based on the value of a specific cell, call it X, another cell would formulate to read zero, until the cell reaches 250,001. then it would take the value of that cell, less 250,000, and multiply the difference by .37 to a maximum of 55,500. I can get two of the three variables to work, but cannot get that max out number to work. It is reading a cell ...

Design Apple Development Security Automobile Network Photography Health Money Travel Shopping Issues Operating systems Drivers Software Programming Tech Home Science Sport Solution