none
Using VBA to get task number of days late? RRS feed

  • Question

  • Hello

    I am trying to figure out a way to create a simple report with number of days late for some main tasks.

    I know on the tracking gantt I can hover on the summary task bar and see the "summary progress" date which shows the progress date of all the sub tasks together up to the summary task.  But I can't seem to find that field in the VBA model.

    Does anyone know how to access that date?  Or a better way to get # of days late for a summary task?

    I have tried searching on this subject but it is always a comparison to baseline (which would be a different question).

    Thanks in advance!

    Art

    Sunday, June 17, 2018 9:25 PM

Answers

  • Hi,

    sorry, you dd ask for a way to see the date of mouse over. As Tom wrote, it does not mean a lot :). But I was too busy to ask for some background information. To see the delay is someting different.

    The "delay" in duration is something like "[Actual Duration]-ProjDateDiff([Start],[Current Date],[Project Calendar])"

    In "ProjDateDiff" there is caluculated what the actual duration should be, so the difference is the delay.

    Anyway: How do you wnat it to be calculated for summary task? Using the formula willalways reflect the weighted ACtual Duartion. So perhaps it is better to use the minimum. This will give you the biggest delay?

    Regards
    Barbara


    Friday, June 22, 2018 11:47 AM
    Moderator

All replies

  • Hi,

    I think you could just use a formula, if you are interested in the date for "Summary Progress":

    ProjDateAdd([Start],[Duration]*[% Complete]/100,[Project Calendar])

    For Summary Tasks use "Maximum".

    Does this give you the expected dates?

    Barbara

    • Proposed as answer by MorphaToo Monday, June 18, 2018 10:14 AM
    Monday, June 18, 2018 8:24 AM
    Moderator
  • Art,

    Barbara's response answers the concern of your second paragraph (keeping in mind that "Summary Progress," like "CompleteThrough," is computed solely to draw the endpoint of a bar on a Gantt chart.  Its use in any further analysis would be fairly dubious.) Exception: I would have suggested using the formula rather than Max Rollup for summary rows.

    If by "number of days late," you want to display the difference between the "Summary Progress" date and either the current date or the Status Date, than it's simple enough to include Date1 computed using Barbara's suggested formula as one of the terms in a custom duration field, e.g. Duration1 = IIf(ProjDateDiff([Date1],[Status Date])>0,ProjDateDiff([Date1],[Status Date]),0).   (Note that the "," or ";" list separator depends on your regional settings. I'm using US, while Barbara is using EUR.)

    This approach is dubious for several reasons: 1) it incorporates an update methodology that accepts incomplete work in the past; 2) It's reliance on duration-weighting of sub-tasks makes the result too easy to misinterpret under real-world circumstances.  Good luck, tom    

    Monday, June 18, 2018 12:56 PM
  • Thanks Barbara, Tom

    Interesting formulas but doesn't match what project is reporting.  I was thinking of using Actual Duration but that doesn't work or ACWP / BCWP * [Duration] but that turns out to be Actual Duration.

    I guess Project doesn't give any insight to this calculation. 

    I am surprised that the project community doesn't have an answer to 'are we on schedule, how many days/weeks are we behind schedule?'.  It's a client question that I get all the time.  My only recourse has been to use the tracking gantt and hover over as many summary tasks to get the full picture.  I guess I just continue with that.

    Appreciate your responses and your time...

    Art

    Friday, June 22, 2018 11:27 AM
  • Hi,

    sorry, you dd ask for a way to see the date of mouse over. As Tom wrote, it does not mean a lot :). But I was too busy to ask for some background information. To see the delay is someting different.

    The "delay" in duration is something like "[Actual Duration]-ProjDateDiff([Start],[Current Date],[Project Calendar])"

    In "ProjDateDiff" there is caluculated what the actual duration should be, so the difference is the delay.

    Anyway: How do you wnat it to be calculated for summary task? Using the formula willalways reflect the weighted ACtual Duartion. So perhaps it is better to use the minimum. This will give you the biggest delay?

    Regards
    Barbara


    Friday, June 22, 2018 11:47 AM
    Moderator
  • Art,

    "Are we on schedule? How many days/weeks are we behind schedule?" is easily answered by a) Finish variance, and/or b) Negative Total Slack.  The first requires an approved Baseline schedule.  The second requires a robust logic-driven schedule network and a single Deadline on your key milestone(s).  Both require a valid schedule progress updating and forecasting methodology.  Neither requires any VBA or formulas.

    Both the title of the thread and the text of your original post implied a different focus.

    Good luck, tom

     

    Friday, June 22, 2018 12:06 PM