Answered by:
How do I calculate latest dates within sub tasks?
Question

Morning all, this is hopefully a simplified example of what I'm trying to do, and I apologise for not being able to cutnpaste this in table format
1 Parent Task
1.1 Child Task 1  Starts 1st Jan with duration 2 days
1.2 Child Task 2  Starts straight after Child Task 1 and has a duration 3 days
1.3 Child Tasks Complete Milestone
It's therefore really easy to have my Milestone as a sucessor to 1.2 and I'll see 5th Jan (i.e. 1st Jan + 2 days + 3 days)
My real example though has many more Child Tasks and they are not all sequential so I think I have a few optiions
1) Have my Milestone reflect every child task and accept the Gannt will be messy, and the links are fixed
2) Link the Milestone FF with Parent Task but I think this will cause a circular reference
3) Use some calculation so my Milestone shows the latest end date for a range of tasks. I know in VBA this would be a variant on DMAX, i.e. I can do a DMAX("End Date", "Task 1.1 to Task 1.2")
I'm fluent with VB, VBA and I'd like to explore Option 3 as it gives greatest flexibility but I am struggling with how to implement it in MSP. For example in MSAccess I can put the code in a Module, call it ona form, put it in a query, similarly in Excel I can stick it in a cell but what is the equivalent in MSP?
Answers

Hi,
if I understand correctly, you want to have the milestone date as latest finish date of subtasks? The easiest way is to move it to a higher outline level. So you will have it on the same level as the summary task which does already give you the latest finish date.
For your VBA question (even I would go for my above suggestion), it could be something like this, if you put in in a module:
Sub MilestoneDate() Dim T As Task For Each T In ActiveProject.Tasks If Not T Is Nothing Then If T.Milestone Then 'Remove constraints to get "clean" finish date of parent T.ConstraintType = pjASAP 'Set Finish Date to ParentFinishDate T.Finish = T.OutlineParent.Finish End If End If Next T End Sub
Regards
Barbara Marked as answer by JohnFol2012 Monday, January 14, 2019 1:06 PM
All replies

Hi,
if I understand correctly, you want to have the milestone date as latest finish date of subtasks? The easiest way is to move it to a higher outline level. So you will have it on the same level as the summary task which does already give you the latest finish date.
For your VBA question (even I would go for my above suggestion), it could be something like this, if you put in in a module:
Sub MilestoneDate() Dim T As Task For Each T In ActiveProject.Tasks If Not T Is Nothing Then If T.Milestone Then 'Remove constraints to get "clean" finish date of parent T.ConstraintType = pjASAP 'Set Finish Date to ParentFinishDate T.Finish = T.OutlineParent.Finish End If End If Next T End Sub
Regards
Barbara Marked as answer by JohnFol2012 Monday, January 14, 2019 1:06 PM
