PDA

View Full Version : [SOLVED] Dependencies and Dates



Sully1440
02-20-2018, 01:44 PM
I need help with dependencies and dates. In the sheet attached, I have a list of predecessors with some of them separated by a comma. I want the start date to begin after the maximum finish date from the one (or two or three) predecessors. I want the code to loop through to setup the linkages such that the start dates and finish dates are all set.

Please help
Thanks,
Jim

Sully1440
02-21-2018, 08:55 AM
Need help writing code for the following logic:



For cells in Column D (D2 to end of list),
Make the Plan Start Date one day after the max of the Predecessors Finish Dates (which can be greater than one predecessor in the list separated by a comma)


For example:
In the attached excel sheet, for task A06, the start date should be one day after the max finish date of task A04 and A05 (Feb 7/18 and Jan 13/18).
Therefore, the start date for task A06 = Feb 8/18

The code should loop through each cell in Column D to the end of the list.

Can someone help me?
Thx,
Jim

Sully1440
02-21-2018, 10:10 AM
I'm thinking I can do this with a formula versus code but I still can't get the multiple criteria to work.
So far I have the formula for single entries =INDEX($E$2:$E$29,MATCH(C3,$A$2:$A$29))

But if I have multiple entries (A15,A16,A17), it doesn't search for the max of these dates in column E......
I attached another sheet to show my formula result....


Help?

Sully1440
02-22-2018, 06:58 AM
Bump...

p45cal
02-22-2018, 12:22 PM
In the attached a user defined function:
Function LatestDate(Predecessor, ActivityColumn, FinishColumn)
Predecessors = Split(Predecessor.Value, ",")
For Each P In Predecessors
LatestFinish = Application.Max(LatestFinish, Application.Index(FinishColumn, Application.Match(Application.Trim(P), ActivityColumn, 0)))
Next P
LatestDate = LatestFinish
End Function
used in cells D3:D29
Since these formulae are dependent on results of these same formulae on other rows, the sheet may automatically calculate each formula more than once - but you probably won't notice this.

Sully1440
02-22-2018, 01:16 PM
This works perfectly. Thank You SO much.

I never would have figured this out.

Thx again. :) :yes