none
Need help on formula to compare task duration to project's overall baseline duration. RRS feed

  • Question

  • Seems like this should be easy, so I must be missing something?

    I need a formula to compare an individual task's start variance (I know how to get this as I need it) to the project's OVERALL BASELINE duration.  I do not want to compare to the project's (current) Duration, and I do not want to compare to the individual task's duration - I can write formulas for each of those - but I need the overall project's basline Dur.  I would think I should be able to get this from the Summary Task and cannot figure out how?

    In other words, I have an overall project Baseline Duration of ~77d and a Duration of ~85d and I want to see how Task N's Start Var of 3d compares to the Project Baseline Duration of 77.  In this example, I am looking for 3.90% variance from 3d/77d, not 3.53% which is 3d/85d. 

    Any help?

    Thanks

    Wednesday, July 18, 2012 2:24 PM

Answers


  • Sub StarVarPerc()
    Dim T As Task
    For Each T In ActiveProject.Tasks
        If Not (T Is Nothing) Then
            If T.Summary = False Then
                If T.StartVariance > 0 Or T.StartVariance < 0 Then
                    T.Text1 = Format(T.StartVariance / ActiveProject.ProjectSummaryTask.BaselineDuration, "0.00%")
                End If
            End If
        End If
    Next T
    End Sub


    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn


    Thursday, July 19, 2012 4:19 AM
    Moderator

All replies

  • Joe --
     
    There is no easy way to accomplish this goal unless you are willing to write VBA code.  When you write a formula, Microsoft Project calculates the formula for every task and summary task individually.  There is no way in a formula to refer to a value from any other task, not even from the Project Summary Task (Row 0 or Task 0).  Remember that formulas in Microsoft Project are similar to formulas in Excel, but they are not identical.  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"

    Wednesday, July 18, 2012 2:41 PM
    Moderator
  • Thanks Dave.

    Guess I wasn't missing something after all.  Doesn't seem all that odd a request - I'm surprised there's not an easier way.

    Well, it's not critical, but if anyone is up to some VBA code, I'll take it.

    Thanks all!

    Wednesday, July 18, 2012 8:51 PM
  • Dave --
     
    Although the request does not seem odd, I will agree, your request defies how formulas actually work in Microsoft Project.  To explore the VBA angle, you might want to repost your question in the Project Customization and Programming user forum at:
     
     
    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"

    Wednesday, July 18, 2012 9:03 PM
    Moderator

  • Sub StarVarPerc()
    Dim T As Task
    For Each T In ActiveProject.Tasks
        If Not (T Is Nothing) Then
            If T.Summary = False Then
                If T.StartVariance > 0 Or T.StartVariance < 0 Then
                    T.Text1 = Format(T.StartVariance / ActiveProject.ProjectSummaryTask.BaselineDuration, "0.00%")
                End If
            End If
        End If
    Next T
    End Sub


    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn


    Thursday, July 19, 2012 4:19 AM
    Moderator
  • That's Perfect!  Thanks all.
    Thursday, July 19, 2012 5:16 PM