none
Task Level custom field with Formulae RRS feed

  • Question

  • Dear Folks 

    I have task level custom field call "Task Status" with lookup values "WIP","Completed" in MS Project 2016

    When i change the Task status to "Completed" then % complete has to update 100% automatically 

    Similarly , When i change the Task status to "WIP" then % complete has to update 50% automatically 

    Any ideas or links would be useful for me

    Regards

    Santosh

    Wednesday, November 6, 2019 3:28 AM

Answers

  • Santosh --

    Interesting question.  In addition to the excellent answer from my colleague, John, I would recommend that you change your thinking about the custom task field named Task Status.  First of all, were you aware that there is a built-in Task field named Status that provides similar functionality to your Task Status custom field.  When you enter progress on tasks in your project, such as using the % Complete field, the Status field automatically calculates one of the following values:  Complete, Late, On Schedule, or Future Task.  The Status field also includes a companion field named Status Indicator that displays a corresponding indicator for each of the Status field values.

    If you have some type of organization requirement for using the Task Status custom field, I would recommend that you change the field from using lookup values to using a formula instead.  Use the formula to test the % Complete value for each task.  If the % Complete value is 100%, the formula should return a "Completed" value for the task.  If the % Complete value is greater than 0% and less than 100%, the formula should return a "WIP" value.  If the % Complete value is 0%, then the formula should return a blank value.

    Just a couple of thoughts to complement the advice that John offered you.  Hope this helps.


    Dale A. Howard [MVP]

    Wednesday, November 6, 2019 6:02 PM
    Moderator

All replies

  • itsmeantosh1982,

    In order to do what you want you will need to use VBA. Custom field formulas work only on extra fields, they cannot change built-in fields (e.g. % Complete). However, why mess around with a custom task status field when you can simply use the built-in percent complete options found on Task > Schedule group,

    Hope this helps.

    John

    Wednesday, November 6, 2019 1:48 PM
  • Santosh --

    Interesting question.  In addition to the excellent answer from my colleague, John, I would recommend that you change your thinking about the custom task field named Task Status.  First of all, were you aware that there is a built-in Task field named Status that provides similar functionality to your Task Status custom field.  When you enter progress on tasks in your project, such as using the % Complete field, the Status field automatically calculates one of the following values:  Complete, Late, On Schedule, or Future Task.  The Status field also includes a companion field named Status Indicator that displays a corresponding indicator for each of the Status field values.

    If you have some type of organization requirement for using the Task Status custom field, I would recommend that you change the field from using lookup values to using a formula instead.  Use the formula to test the % Complete value for each task.  If the % Complete value is 100%, the formula should return a "Completed" value for the task.  If the % Complete value is greater than 0% and less than 100%, the formula should return a "WIP" value.  If the % Complete value is 0%, then the formula should return a blank value.

    Just a couple of thoughts to complement the advice that John offered you.  Hope this helps.


    Dale A. Howard [MVP]

    Wednesday, November 6, 2019 6:02 PM
    Moderator