MS Project Custom Field Formula

philcuddy,

I'm not a big fan of the Switch statement, nor am I a fan of complex formulas but I think the following formula will do what you want:

IIf([% Complete]=0 And [Scheduled Start]<[Current Date] Or [% Complete]<100 And [Scheduled Finish]<[Current Date],"overdue",IIf([% Complete]<100 And [Scheduled Start]<[Current Date],"in progress",IIf([% Complete]=0 And ([Scheduled Start]>=[Current Date] And [Scheduled Start]<=[Current Date]+7),"due soon","")))

However, I question your logic. First, just because a task hasn't started and it was scheduled to start before the current date, I wouldn't necessarily consider that task as overdue. It could be a fixed duration task wherein the resource won't actually start on it until a couple of days in. Second, in Project, formulas only apply to the current task line. Unless you specifically select to use formulas for  calculation of task and group summary rows (this is an option on the Custom Fields window), nothing will be show for summary lines.

Just for reference, have you looked at the Status field? It already does what you want although the criteria is slightly different.

John

philcuddy,

I can't explain why you had to set and unset the formula option for summary rows. It's possible your file has some corruption. You could try saving the file as XML and then re-opening in Project. That normally clears corruption but you will lose any custom formatting, so I'd only do it if you don't have custom formatting.

I'm not a fan of the Switch statement simply because I never use it. I find the "If" construct to be more useful but it's strictly a matter of preference. I also mentioned that I'm not a fan of complex formulas. They are difficult to enter correctly, difficult to troubleshoot when they don't work, and can be easily lost (i.e. not saved in the Project Global like a VBA module). Also, be advised that in Project nesting of "If" statements is limited to 15. I don't know if that also applies to the Switch statement.

I might not have made it clear that formulas in Project are much more limited than in Excel. As I said previously, a formula can only operate on data on the current task line. For example, you cannot take the Start date of task ID 3 and use it in a formula for Task ID 4. Some Project level data is available (e.g. Status Date, Project Start), but that's limited. That's why I use VBA much more than formulas in custom fields.

Did you take a look at the built-in Status field? It takes a little learning to understand exactly what it uses for criteria, but it does essentially provide the information you are attempting to get with your formula.

If this answered your question, please mark it as answered.

John

Hi John, many thanks.

It appears there is a bug in my mpp file / ms project. I used your formula and it caused the same error as mine (showing overdue for future summary rows). On the custom fields screen i then selected 'formula' for 'calculation for tasks and group summary rows' and the formula worked as expected. I then undid this option and things continued working correctly. Strange behaviour...

Regarding the use of the switch statement, what alternative would you suggest. I am very much a beginner in ms project formulae and am very interested in learning best practice, but find very little information online.

Regarding logic, i see what you mean, but i would like to highlight tasks that should have started or finished, there may well be a good reason, but i still think highlighting tasks that i would consider overdue as being a function of my role as a planner working alongside the PM.

Cheers