Status date in a filter

Home Forums Microsoft Project Discussion Forum Status date in a filter


  • This topic is empty.
Viewing 6 reply threads
  • Author
    • #409783
      Avatar photoOliver Gildersleeve

      In Project 2016 Pro, how can I access in a filter the date entered in Project > Project information > Status? I am trying to find tasks needing to be updated in a long schedule with many parallel paths. The filter would look for tasks with Resume dates before the Status date. I can’t use the Status column because it shows as “Late” those tasks that have been split by “Move incomplete parts to the Status date”, even after they have been updated by “Mark on track”. I can’t use “now()” because I often run my report of tasks needing updates a few days after the scheduler has set the Status date. Thanks in advance.

    • #409787
      Avatar photoLarry Christofaro

      Good question Oliver. Someone might have a more elegant answer but you can use a custom task field that contains the status date. Go to Custom Fields, and create a custom field with the formula [Status Date]. Then use that field in your formula. Good luck…

    • #409794
      Avatar photoOliver Gildersleeve

      Wonderful! Your guidance lead me to Google search for “Microsoft Project Status Date” in which I found Darren Deason’s July 3, 2014, “Working With [Status Date] Field in Microsoft Project Formulas.” It showed that [Status Date] can be used directly in a formula without using another field first. So, I put together the following in a Flag field to identify tasks needing updates, even those that had been split by “Move incomplete parts to the Status date.”

      IIf([Actual Start]=projdatevalue(“NA”),IIf([Start]<[Status Date],Yes,No),No)
      Or IIf([Actual Start]<>projdatevalue(“NA”),IIf([Resume]<[Status Date],Yes,No))

      The first IIF identifies unstartled tasks with a planned Start date before the Status Date.
      The second IIF identifies actually started tasks with Resume before the Status Date.

      Finally, for a view, I set up a filter for the Flag field = Yes.

      But, where do you find other variables such as [Status Date]. It doesn’t appear in the drop down list when you are building a formula. Are there other such variables?

    • #409803
      Avatar photoOliver Gildersleeve

      Oops, a little more testing provided the following improved formula:

      IIf([Actual Start]=projdatevalue(“NA”),IIf([Start]<[Status Date],Yes,No),No)
      Or IIf([Actual Start]<>projdatevalue(“NA”),IIF([Actual Finish]=projdatevalue(“NA”),IIf([Resume]<[Status Date],Yes,No),No),N0)

    • #409804
      Avatar photoLarry Christofaro

      Nice work…:-)

    • #414202

      In looking into the filter solution, there are a plethora of start and finish fields you could use in mapping to the [Status Date] (somewhat hidden data field in MS Project). Essentially you’ll need to create a Flag field (no need to use a date field unless your date calculating on the fly) to hold the evaluation of % Complete < 100, with a Finish or Start date (actual or otherwise) that is “less than” < the [Status Date] field. You can string as up to 7 Boolean And’s in between decision indicator (YES or NO) for the result but doing so becomes quite complex. For my team I made it simple, so long as the [Start] is before the [Status Date](which could be months ago) AND the [Finis] is before the [Status Date] and the task isn’t complete (100%) I flag it. I chose this approach because I want to see ALL TASKS, needing Status for the Current week and we do weekly status and metrics reporting; if you just want to see everything needing status remove the Finish block and you’ll get all open tasks needing attention.

      IIf((([% Complete]<100) And [Start]<[Status Date]) And [Finish]<[Status Date],”Yes”,”No”)

    • #481216

      This is how I did it. Not trying to answer quite the same question, but the formula can be modified. I was trying to create a filter for “unstatused tasks”.

      I Created new custom field. I used Flag1 (I renamed Unstatused Tasks).
      I used this formula: IIf(((([Start]<=[Status Date]) And ([% Complete]=0)) Or (([Finish]<=[Status Date]) And ([% Complete]<>100))),”Yes”,”No”)

      Then I hid that field (because I like my schedules clean), and I created a new filter.

      Field name: Flag 1 (Unstatused Tasks), Test: equals, Value: Yes
      Field Name: Active, Test: equals, Value: Yes

      Now at the end of each status period, I just run the filter to see if I’ve missed collecting or entering status.


Viewing 6 reply threads
  • You must be logged in to reply to this topic.