I am terrible with formulas, and can’t seem to find a general formulas guide for MS Project anywhere. I’ve played around with some different formulas with no luck. What I am looking to do is come up with a simple formula to use in a custom field that I can use as my “Status Needed” column, where I can quickly identify tasks “calling for” status within the given reporting period. I just want something that tells me:
1) This task was due to start by the status date, but shows no Actual Start and/or has a % Complete of 0%.
2) This task was due to finish by the status date, but shows no Actual Finish and/or has a % Complete less than 100%.
Ideally, within this formula I’d like some sort of differentiation between the two elements, which I could then apply graphical indicators to. Meaning, have #1 above read something like “Start Date in Past” and #2 read something like “Finish Date in Past” within the custom Text field I’d be using. Hopefully that makes sense.
Also, if it could be easily integrated and differentiated within this formula, I wouldn’t mind a third element that accounted for in-progress tasks that were NOT forecasted to finish by the status date. Not sure of the best way to capture that, I made a custom filter for “Resume Dates in the Past” that seems to capture this pretty well, but if I could somehow integrate this information to the formula I’m asking about here, and have it all in one place, even better.
And since I’m here, I’ll press my luck and ask another question. Separate field, something that catches Actual Starts and Actual Finishes in the future (past the status date).
Thanks for any help!
In the past, I’ve done similar things to your needs using views with filters. I don’t have Project available to me at the moment to give you specific filter solutions, but I’ve found using filter logic much simpler than attempting to create custom field formulas. In addition, views/filters can easily be made “global” which makes them usable across every project.
I’d recommend you create multiple views, one for each scenario at first. Then with individual views/filters working, you can work to combine the views into one consolidated view if necessary.
I’d also recommend building your filters using a step by step approach as well. Getting Project’s filters to work correctly with multiple conditions can be a bit tricky, specifically when you get into combined and/or scenarios. So if possible, keep the filter logic as simple as possible
With that said, this solution will require the use of baseline data.
Using your first example, create the view with required columns. For example, task name, ID, baseline start, planned start, actual start, resource names and so on.
Then create the view filter in a step by step process.
Step 1, Task with no Actual Start date
Step 2, Add filter logic to only include tasks whose Baseline Start is less than Status date
Step 3 add in additional logic specific to that view, if any.
With the basic view filter criteria working, you can then tweak the view to, for example, sort in ascending Baseline Start date so the tasks furthest beyond their planned start date show at the top.
As I mentioned, I don’t have Project readily at hand, so if Status is not available as a filter field, you can configure your filter to prompt you to enter your status date when the view/filter is opened. Examples of how to have your filter prompt you can easily be found on the web.
I hope this helps.