none
Formula Driven Lag/Lead Time RRS feed

  • Question

  • Hi, is it possible to enter a formula into the Predecessor and Successor fields so that the lead and lag times can be recalculated to different values if certain date scenarios change?

    Thanks

    Dan

    Tuesday, September 13, 2016 3:22 PM

Answers

  • Dan,

    Okay, here's the macro. A few things to note. Tasks that represent the order date need to have Flag1 set. If the pay date falls on a non-working day, it will be moved to the next working day. The code is leap year aware.

    Hope this helps.

    John

    'This macro sets the date for a successor task (invoice payment) to be
    '   the last calendar day of the month following a predecessor task (order date)
    '   Tasks are selected by setting Flag1. Note: this procedure does leave a
    '   start-no-earlier-than constraint on all shifted tasks
    '   And if shifted successor task falls on a non-working day, it will automatically
    '   be shifted to the next working day.
    'written by John - Project 9/14/16 7:00 pm
    Option Explicit
    Public t As Task
    Public OrdMon As Integer, OrdYr As Integer, PayMon As Integer, PayYr As Integer
    Public PayDt As Integer

    Sub NextMonthAfterOrder()
    'cycle through each all non-summary tasks looking for Flag1
    For Each t In ActiveProject.Tasks
        'skip over blank lines
        If Not t Is Nothing Then
            'only look at Start time of non-summary tasks
            If t.Summary = False And t.Flag1 = True Then
                'find date parameters for this task & set up date parameters for successor
                OrdMon = Month(t.Start)
                OrdYr = Year(t.Start)
                PayMon = OrdMon + 1
                PayYr = OrdYr
                If OrdMon = 12 Then PayMon = 1: PayYr = OrdYr + 1
                'find last day of pay month
                LastDay
                'now set successor start date
                ActiveProject.Tasks(CInt(t.Successors)).Start = CDate(PayMon & "/" & PayDt & "/" & PayYr)
            End If
        End If
    Next t
    End Sub
    Private Sub LastDay()
    Select Case PayMon
        Case 4, 6, 9, 11
            PayDt = 30
        Case 1, 3, 5, 7, 8, 10, 12
            PayDt = 31
        Case 2
            PayDt = 28
            'account for leap years
            If InStr(1, CStr(PayYr / 4), ".") = 0 Then PayDt = 29
    End Select

    End Sub

    • Marked as answer by Dan Kient Thursday, September 15, 2016 8:49 AM
    Thursday, September 15, 2016 2:41 AM

All replies

  • Dan,

    No, but give us an example and we'll try to help you figure out a way to get what you need.

    For reference, built-in fields (e.g. Duration, Start, Finish, Predecessors, Successors, etc.) cannot be customized other than through VBA.

    John

    Tuesday, September 13, 2016 4:46 PM
  • Hi Dan,

    Unfortunately, there is no field where one could enter a formula that would affect relationship leads/lags.

    Conditional modification of leads/lags could easily be achieved using VBA, though the effort is not trivial.

    Good luck, tom

    Tuesday, September 13, 2016 4:51 PM
  • Tom,

    I don't think it is "unfortunate", but just the way it is, and as it should be. At some point the planner has to do some actual planning. The lag/lead is an input which is determined by the nature and circumstances of the project and the tasks, and as interpreted by the project planner. What can be done with software and automation has not quite replaced what can be done by humans (yet).

    Wednesday, September 14, 2016 2:53 AM
  • Thanks All for your replies.

    Use Case is:-

    Task A - Place order for new crane - Start/Finish date 14th September

    Task B - Make payment for new crane - Start/ Finish date 31st October

    Task B needs to Always be the final date of the following month from placing the order ie Task A.  If Task A slips to say 29th September then Task B remains the same.  If Task A slips into October then task B date would recalculate to 30th November.

    We have a current workaround that involves a custom formula on Task row A which calculates the "next" month end date in relation to the task date.  We then copy that date and Paste Special it to the start date of Task B.  We have a whole load of these such task sets in each project.  I take Trevor's point about planning, which in this case is the setting of the place order task, thereafter the make payment task lag time is really a business process action and follows a consistent rule.   If we could automate this it would be very helpful.

    Thanks

    Dan

    Wednesday, September 14, 2016 7:54 AM
  • Dan,

    Thanks for describing the scenario. Yes, that is fairly easy to implement with VBA. It is very similar to a common request that particular task always start at the beginning of shift on a particular day regardless of when the predecessor task finishes. I'll dig up the macro I have on that and tweak it for your needs and post back, hopefully later today.

    John

    Wednesday, September 14, 2016 4:07 PM
  • thanks John that will be great,

    Dan

    Wednesday, September 14, 2016 6:52 PM
  • Dan,

    Okay, here's the macro. A few things to note. Tasks that represent the order date need to have Flag1 set. If the pay date falls on a non-working day, it will be moved to the next working day. The code is leap year aware.

    Hope this helps.

    John

    'This macro sets the date for a successor task (invoice payment) to be
    '   the last calendar day of the month following a predecessor task (order date)
    '   Tasks are selected by setting Flag1. Note: this procedure does leave a
    '   start-no-earlier-than constraint on all shifted tasks
    '   And if shifted successor task falls on a non-working day, it will automatically
    '   be shifted to the next working day.
    'written by John - Project 9/14/16 7:00 pm
    Option Explicit
    Public t As Task
    Public OrdMon As Integer, OrdYr As Integer, PayMon As Integer, PayYr As Integer
    Public PayDt As Integer

    Sub NextMonthAfterOrder()
    'cycle through each all non-summary tasks looking for Flag1
    For Each t In ActiveProject.Tasks
        'skip over blank lines
        If Not t Is Nothing Then
            'only look at Start time of non-summary tasks
            If t.Summary = False And t.Flag1 = True Then
                'find date parameters for this task & set up date parameters for successor
                OrdMon = Month(t.Start)
                OrdYr = Year(t.Start)
                PayMon = OrdMon + 1
                PayYr = OrdYr
                If OrdMon = 12 Then PayMon = 1: PayYr = OrdYr + 1
                'find last day of pay month
                LastDay
                'now set successor start date
                ActiveProject.Tasks(CInt(t.Successors)).Start = CDate(PayMon & "/" & PayDt & "/" & PayYr)
            End If
        End If
    Next t
    End Sub
    Private Sub LastDay()
    Select Case PayMon
        Case 4, 6, 9, 11
            PayDt = 30
        Case 1, 3, 5, 7, 8, 10, 12
            PayDt = 31
        Case 2
            PayDt = 28
            'account for leap years
            If InStr(1, CStr(PayYr / 4), ".") = 0 Then PayDt = 29
    End Select

    End Sub

    • Marked as answer by Dan Kient Thursday, September 15, 2016 8:49 AM
    Thursday, September 15, 2016 2:41 AM
  • thanks John, I will give this a try later.

    Appreciate your help.

    Dan

    Thursday, September 15, 2016 8:49 AM
  • Dan,

    You're welcome and thanks for the feedback.

    John

    Thursday, September 15, 2016 2:00 PM