Quick Links

Status date in a filter

Home Forums Discussion Status date in a filter


  • This topic has 5 replies, 3 voices, and was last updated 3 months, 1 week ago by Oliver Gildersleeve.
Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
  • #409783 Reply

    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 Reply
    Larry 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 Reply
    Oliver 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 Reply

    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 Reply
    Larry Christofaro

    Nice work…:-)

    #414202 Reply

    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”)

Viewing 6 posts - 1 through 6 (of 6 total)
Reply To: Status date in a filter
Your information:

47 − = 40