# 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 cut-n-paste this in table format

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

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?

Monday, January 14, 2019 10:35 AM

• 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()

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 Monday, January 14, 2019 1:06 PM
Monday, January 14, 2019 10:59 AM

### 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()

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 Monday, January 14, 2019 1:06 PM
Monday, January 14, 2019 10:59 AM
• Hi Barbara,

I really like the first option as it's the simplicity I was unable to see.

Many thanks!

Monday, January 14, 2019 11:39 AM