none
Adjusting Lags with Task Calendar - DateDiff, DateSubtract.....etc. RRS feed

  • Question

  • I have a problem in which I need to make an adjustment to the Lag’s in a Successor because I am comparing schedules in another Schedule Tool to that of MS Project.  The schedule I am analyzing has 5 Calendars.  The correction I am working on is to assess the Lag using the calendar of the Successor rather than the Calendar of the Primary Task.  To do this I am trying to get a query of MS Project of the Number of day between the Completion of the Primary Task and the Start of the Successor using the Calendar of the Successor. 

     

    I found the “DateDiff function that allow is supposed to allow for the evaluation of the number of days between two days using a specified calendar.  I have been having some difficulty in getting it to work.

     

    I would appreciate your help with this.

     

     

    Friday, January 20, 2012 8:38 PM

Answers

  • DateDifference is on the application object.

    Here is my same code but showing how to get handles on the calendar of the tasks involved in a link.

    Sub LagStuff()
    Dim t As Task
    Dim d As TaskDependency
    
    For Each t In ActiveProject.Tasks
        If Not (t Is Nothing) Then
            For Each d In t.TaskDependencies
                If d.Lag > 0 Then
                    Select Case d.Type
                        Case pjFinishToStart
                            Debug.Print Application.DateDifference(t.Finish, d.To.Start, ActiveProject.BaseCalendars("7dayCal"))
                            Debug.Print d.From.Calendar
                            Debug.Print d.To.Calendar
                    
                        Case pjStartToStart
                            Debug.Print Application.DateDifference(t.Start, d.To.Start, ActiveProject.BaseCalendars("7dayCal"))
                            Debug.Print d.From.Calendar
                            Debug.Print d.To.Calendar
                        Case pjFinishToFinish
                            Debug.Print Application.DateDifference(t.Finish, d.To.Finish, ActiveProject.BaseCalendars("7dayCal"))
                            Debug.Print d.From.Calendar
                            Debug.Print d.To.Calendar
                    End Select
                End If
            Next d
        End If
    Next t
    End Sub
    


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn
    • Marked as answer by thiirane Saturday, January 21, 2012 8:25 PM
    Saturday, January 21, 2012 8:19 PM
    Moderator

All replies

  • thiirane --
     
    The reason this is not working is because you CANNOT write a formula to compare any kind of values between two different tasks.  You could use a formula to calculate the difference between any two dates on the SAME task, but not two dates on two different tasks.  Hope this helps.

    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    Friday, January 20, 2012 8:46 PM
    Moderator
  • There were a couple of Typos ommissions in the previous code corrected below.....

     

    O.K.  Well that's too bad but I can still make use of DateDiff() 

    Consider two tasks linked FS+4 each 5 days long with a Primary task that has a 5 day calendar and its successor with a 7 day calendar. 

    I want to evaluate the length of the lag using the 7 day calendar.

     

     

     

    Sub Lag_Compensate()

    Dim ss As String
    Dim s2() As String

    Dim T_Finish As Variant
    Dim Succ_Start As Variant
    Dim stSucc_Calendar As String
    Dim lagg As Date

     


        total_Tasks = ActiveProject.Tasks.Count
       
         Dim projApp As MSProject.Application
        
            On Error Resume Next
            Set projApp = GetObject(, "MSProject.Application")
           
        
        
        
    '   For Successors
       
       
       
            For i = 1 To total_Tasks
                On Error Resume Next
                If ActiveProject.Tasks(i).Name = Null Then GoTo Label32
                stCalendarT = ActiveProject.Tasks(i).Calendar

                If ActiveProject.Tasks(i).Successors = Empty Then GoTo Label32
               
               
               
                s2 = Split(ActiveProject.Tasks(i).Successors, ",")
               
                indx = UBound(s2)

                ' Msgbox ("indx ( " & i & " ) = " & indx(i))

                    If indx = -1 Then GoTo Label32
               
                    For j1 = 0 To indx
                        s2(j1) = Trim(s2(j1)) 'remove spaces
                       
                       
                        'find Successor Number.
                       
                       
                        RelPos = InStr(1, s2(j1), "FS")
                        If RelPos > 0 Then
                        GoTo Label20
                        End If
                        RelPos = InStr(1, s2(j1), "FF")
                        If RelPos > 0 Then
                        GoTo Label20
                        End If
                        RelPos = InStr(1, s2(j1), "SS")
                        If RelPos > 0 Then
                        GoTo Label20
                        End If
                       
    Label20:

                        signPos = InStr(1, s2(j1), "+")
                        If signPos > 0 Then
                        GoTo Label21
                        End If
                        signPos = InStr(1, s2(j1), "-")
                        If signPos > 0 Then
                        GoTo Label21
                        End If
                       
                       
                       
    Label21:

                    OldLag = Trim(Mid(s2(j1), RelPos + 3, Len(s2(j1)) - 7))
                   
                    'LagDays = CInt(OldLag)
                    intSucc = Trim(Mid(s2(j1), 1, RelPos - 1))

                        stSign = Mid(s2(j1), signPos, 1)

                       Rel = Mid(s2(j1), RelPos, 2)
                       
                        stSucc_Calendar = ActiveProject.Tasks(CInt(intSucc)).Calendar
                       
                        If stSucc_Calendar = stCalendarT Then
                       
                            GoTo Label32
                       
                        Else
                       
                            T_Finish = CDate(Trim(Mid(ActiveProject.Tasks(i).Start, 1, 10)))
                           
                            Succ_Start = CDate(Trim(Mid(ActiveProject.Tasks(i).Finish, 1, 10)))
                           
                            'Now find the new Lag using the Successor Calendar

                            lagg = DateSubtract(Succ_Start, OldLag, stSucc_Calendar)
                           
                          End If
                         

                       
                       
                    Next j1
               
           
       
     
       
       
    Label32:
       
            Next i

    End Sub


    • Edited by thiirane Saturday, January 21, 2012 12:13 AM
    Saturday, January 21, 2012 12:12 AM
  • I can not seem to get any custom calendar to work with DateDiff, or DateSubtract functions. Anybody know why?
    Saturday, January 21, 2012 1:57 AM
  • OK A few things.

    The calendar in the DateDifference function has to be an object so you have to get at it via the Activeproject.Basecalendars collection.

    Also, there is a collection called TaskDependencies for each task that will give you easier access to things like the link type and lags and such and also gives you hooks to the from and to task object for the tasks in the link.

    My code below just prints the datedifference in the Immediate window for the various link types. it is not exactly what you need but should get you closer and with fewer lines of code.

    In my project I created a base calendar called "7DayCal".

     

    Sub LagStuff()
    Dim t As Task
    Dim d As TaskDependency
    
    For Each t In ActiveProject.Tasks
        If Not (t Is Nothing) Then
            For Each d In t.TaskDependencies
                If d.Lag > 0 Then
                    Select Case d.Type
                        Case pjFinishToStart
                            Debug.Print Application.DateDifference(t.Finish, d.To.Start, ActiveProject.BaseCalendars("7dayCal"))
                    
                        Case pjStartToStart
                            Debug.Print Application.DateDifference(t.Start, d.To.Start, ActiveProject.BaseCalendars("7dayCal"))
                            
                        Case pjFinishToFinish
                            Debug.Print Application.DateDifference(t.Finish, d.To.Finish, ActiveProject.BaseCalendars("7dayCal"))
                    End Select
                End If
            Next d
        End If
    Next t
    End Sub
    


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn
    Saturday, January 21, 2012 5:48 PM
    Moderator
  • Brian,

    Very nice.  Thanks for the tip on the TaskDependencies and BaseCalendars.  I did some probing.  There does not appear to be an easy way to query the calendar for the successor or predecessor this way however. Is this true? As I indicated earlier, this schedule has 5 calendars.

    I am very appreciative of your help here.  Thank you.

     

    Saturday, January 21, 2012 7:04 PM
  • Well you dont really query the calendar for this. The calendar knows nothing of successors or predecessors. Calendars only track working days and non-working days.

    my code above will return to you the datedifference between the From and To points of a single task link using the specified calendar.

     

    If you wanted to use a different calendar then you could just specify that calendar where my code says "7dayCal"


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn
    Saturday, January 21, 2012 8:00 PM
    Moderator
  • Hi Brian,

    Yes... Precisely, How would you do this using collections as you have shown above.  I went through the different methods and properties but could not find a way to ID a calendar that would be associated with a successor or predecessor.  Often I have 20 predecessor or successors tasks, each potentially with a different calendar.   You have done great here to help me and I am willing to check this question as answered but would like to know if there is a way to capture this information.  Otherwise, I will be doing it the brute-force approach as I have attempted above.   Thanks again.

    Saturday, January 21, 2012 8:09 PM
  • One more thing.  I am using MS Project 2007.  I still am having problems with DateDifference().  Project accepts DateDiff() but there is no argument for Calendar in this function. 
    Saturday, January 21, 2012 8:16 PM
  • DateDifference is on the application object.

    Here is my same code but showing how to get handles on the calendar of the tasks involved in a link.

    Sub LagStuff()
    Dim t As Task
    Dim d As TaskDependency
    
    For Each t In ActiveProject.Tasks
        If Not (t Is Nothing) Then
            For Each d In t.TaskDependencies
                If d.Lag > 0 Then
                    Select Case d.Type
                        Case pjFinishToStart
                            Debug.Print Application.DateDifference(t.Finish, d.To.Start, ActiveProject.BaseCalendars("7dayCal"))
                            Debug.Print d.From.Calendar
                            Debug.Print d.To.Calendar
                    
                        Case pjStartToStart
                            Debug.Print Application.DateDifference(t.Start, d.To.Start, ActiveProject.BaseCalendars("7dayCal"))
                            Debug.Print d.From.Calendar
                            Debug.Print d.To.Calendar
                        Case pjFinishToFinish
                            Debug.Print Application.DateDifference(t.Finish, d.To.Finish, ActiveProject.BaseCalendars("7dayCal"))
                            Debug.Print d.From.Calendar
                            Debug.Print d.To.Calendar
                    End Select
                End If
            Next d
        End If
    Next t
    End Sub
    


    Brian Kennemer – DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn
    • Marked as answer by thiirane Saturday, January 21, 2012 8:25 PM
    Saturday, January 21, 2012 8:19 PM
    Moderator