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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    1
    Sign in to vote 
    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

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    1
    Sign in to vote
    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
  • Question
    Sign in to vote
    1
    Sign in to vote 
    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    That's Perfect!  Thanks all.
    Thursday, July 19, 2012 5:16 PM