none
How to show the overdue, Late, Onschedule & Completed tasks using MSP 2010 RRS feed

  • Question

  • Hi,

    I am using this formula Switch([Finish]>[Baseline Finish] And [Baseline Finish]<[Status Date] And [% Complete]<100,"Overdue",[% Complete]=100,"Complete",[Total Slack]<0,"Late",[Total Slack]>=0,"On Schedule") in the custom field of the "Text Coulmn", but it isn't working for the Overdue tasks. It doesn't honor the "Status Date", and shows overdue for all the tasks which has paased their baseline date. Does anyone know how to show the status of the project in terms of overdue, Late, Onschedule & Completed tasks using MSP 2010.

    Thanks in advance.

    Sunday, January 10, 2016 10:15 PM

All replies

  • AP04,

    I'm not sure what you may be doing wrong but I set up a simple test file with 4 tasks, each to represent one of your formula's conditions. The resulting screen shot shows what I get (i.e. as expected). Note, all tasks are auto-scheduled, using the Standard calendar and the status date is set at 1/29/16.

    I'm using Project 2010 Pro fully updated with the December 2015 Public Update. Is you installation fully updated?

    If you are fully updated I suggest you emulate my test file and see if that works for you. If it does, take a closer look at your live file and see how it is different from the test file.

    John



    • Edited by John - Project Monday, January 11, 2016 12:19 AM sugestion
    Monday, January 11, 2016 12:02 AM
  • Hi John,

    Many thanks for your response. Referring to your example above, if the status date is 01/29/16, then "task c" should also be overdue as it has passed the status date, but it is not. In my opinion both the tasks b & c should be overdue, but it isn't because there is an error in the formulae not honoring the status date. Let me know if I still missing something.

    AP

    Monday, January 11, 2016 1:18 AM
  • AP04,

    Yeah, you are missing something. Let's take the tasks one at a time.

    task a - it fails the first criteria (Baseline Finish not greater than Status Date) but meets the second (100% complete), hence "complete"

    task b - it meets the first criteria, hence "Overdue"

    task c - it fails the first three criteria (Finish not greater than Baseline Finish, not 100% complete, Total Slack not less than 0), hence "On Schedule"

    task d - it fails the first and second criteria but meets the third, hence "Late"

    Does that help?

    John

    Monday, January 11, 2016 1:46 AM
  • Hi John,

    I am agree to what you have said about tasks a, b & d.

    But, why can't we say task c is overdue, considering it has went pass throught its status date and is not 100% complete.

    AP

    Monday, January 11, 2016 2:50 AM
  • AP,

    No, it hasn't. Let's break it down further. The baseline finish is 1/22/16 5:00 pm. The schedule finish is also 1/22/16 5:00 pm, so right off the bat, the very first part of the first criteria is not met (i.e. finish is NOT less than baseline finish). Therefore, the switch statement evaluates the next criteria, which is % complete = 100%. It also fails that criteria, so it goes to the third criteria, which is total slack < 0. It also fails that criteria, so the only remaining criteria is total slack >= 0 and it does meet that criteria, so the result is "On Schedule".

    That's why we can't say it is overdue because according to the switch statement criteria, it is not.

    Now does it make sense?

    Now that I've answered your basic question, let's examine a couple other things.

    First, have you considered using the built-in Status field? It already provides the type of information you are seeking.

    Second, if you decide you still want to use a custom field formula, there are some edits you can make to your Switch statement.

    a. The Status Field does NOT determine if a task if on-time, early, late or otherwise. It is simply a point in time for evaluation of the project's progress. Therefore it should not be part of the formula.

    b. In any multiple criteria function, the first thing to do is to eliminate any elements that do not need to be considered. In this case, if a task if 100% complete, no further evaluation is needed, so that should be the first criteria, not the second.

    John


    • Edited by John - Project Monday, January 11, 2016 2:28 PM more considerations
    Monday, January 11, 2016 3:09 AM
  • John,

    First, have you considered using the built-in Status field? It already provides the type of information you are seeking.   "Yes, I have used the built-in Status field, but it doesn't show you the overdue tasks".

    Second, if you decide you still want to use a custom field formula, there are some edits you can make to your Switch statement. " All I wanted to see is the overdue tasks against the baseline, regardless whether it is built-in formula or have to create a new one."

    a. The Status Field does NOT determine if a task if on-time, early, late or otherwise. It is simply a point in time for evaluation of the project's progress. Therefore it should not be part of the formula.

    b. In any multiple criteria function, the first thing to do is to eliminate any elements that do not need to be considered. In this case, if a task if 100% complete, no further evaluation is needed, so that should be the first criteria, not the second.

    Is there a way you know of how to show the overdue tasks against the baseline.

    John



    Monday, January 11, 2016 10:29 PM
  • What exactly is your definition of "overdue"?
    I think that the obvious one would be that the task was originally (ie baseline) scheduled to finish sometime in the past relative to the status date, but the currently scheduled finish date is later than the baseline finish.

    With this simple definition, it doesn't really matter whether or not the task has actually started, ie has an actual start, or whether the scheduled finish is to the left or right of the status date.

    From this simple definition, you may also want to exclude tasks which have an actual finish, even if the actual finish is later than the baseline finish, ie "overdue" can only apply to tasks where actual finish = NA and/or % complete < 100%.

    Why do you say that Total Slack is part of the definition of "Late"?

    The status date is just the boundary between the past and the future, and it is not an attribute of a particular task.

    Monday, January 11, 2016 11:13 PM
  • What exactly is your definition of "overdue"?
    I think that the obvious one would be that the task was originally (ie baseline) scheduled to finish sometime in the past relative to the status date, but the currently scheduled finish date is later than the baseline finish. "That's right. Any task that isn't 100% complete on its baseline finish date is overdue for me."

    With this simple definition, it doesn't really matter whether or not the task has actually started, ie has an actual start, or whether the scheduled finish is to the left or right of the status date. "I am not taking Actual Start/Finish into consideration at all."

    From this simple definition, you may also want to exclude tasks which have an actual finish, even if the actual finish is later than the baseline finish, ie "overdue" can only apply to tasks where actual finish = NA and/or % complete < 100%.

    Why do you say that Total Slack is part of the definition of "Late"? "I have deadline aginst some milestones and that contributes to the Total Slack, and the task which has Total Slack<0, I am considering them as Late as they may delay the milestone meeting its deadline.

    The status date is just the boundary between the past and the future, and it is not an attribute of a particular task. "The formula I am using is not applying the status date to calculate the Overdue task."


    Tuesday, January 12, 2016 12:17 AM
  • AP04,

    Okay, based on the definition of "overdue" and "late" you gave in your response to Trevor, how about the following formula.

    Text1=IIf([% Complete]=100,"Complete",IIf([Scheduled Finish]>[Baseline Finish],"Overdue",IIf([Total Slack]<0,"late","On schedule")))

    John

    Tuesday, January 12, 2016 2:25 AM
  • Jhon,

    Have just used this formula "IIf([% Complete]=100,"Complete",IIf([Scheduled Finish]>[Baseline Finish],"Overdue",IIf([Total Slack]<0,"late","On schedule")))", it shows Overdue & Late tasks as "Overdue" only, it doesn't show late tasks.

    Tuesday, January 12, 2016 3:46 AM
  • The formulae are interesting in the way that a crossword puzzle is interesting. Interesting but not useful.
    There is no need to invent some new words and then sweat over their definitions.
    This is a "solution" for a "problem" which does not exist.

    Everything needed to measure progress, and assess it in comparison to (what?) usually the baseline, is already built in to the functionality of MSP.

    It is all in the tracking table, the entry table, the work table, the cost table, the variance table and the baseline table. Its in the status field and the variance fields and all of the fields already have documented and established working definitions. It's even graphically displayed in the coloured bars in the various charts and grids in the various views. Finally, it is in the appropriate groups, filters and reports.

    Just hoping to help.

    Tuesday, January 12, 2016 4:18 AM
  • Think I have asked too many questions, I better stop now and accept the above mention lines as the "solution" for a "problem" which does not exist.

    Thanks a lot for your time.

    Tuesday, January 12, 2016 4:48 AM
  • AP04,

    First, let me say that I agree with Trevor in that Project already has several built-in functionality for tracking progress. However, I do realize that some users want/need additional custom metrics for their particular project. That may or may not be true in your case but I think you should examine what it already available before embarking on customization.

    With regard to you trying the last formula I gave you. Because the Switch and If statements examine criteria from left to right, the first true statement will determine the output, even though other criteria following may also be true. You didn't specify whether "overdue" is more or less important than "late". Indeed in the test file I posted earlier in this thread, task b is both overdue and late, however, since the overdue criteria is tested first, that result is what prevails.

    John

    Tuesday, January 12, 2016 3:56 PM