# Project status formula

• ### Question

• I have been working on this formula

IIf([Scheduled Duration]=0,(IIf([% Complete]=100,"Complete",IIf([Scheduled Finish]<Now()+7 And [% Complete]<100 And [% Complete]>=80,"On Track",IIf([Scheduled Finish]<Now()+7 And [% Complete]<80,"Future Task","At Risk")))),IIf([% Complete]=100,"Complete",IIf([% Complete]>=100*(Abs(ProjDateDiff([Scheduled Start],Now())/ProjDateDiff([Scheduled Start],[Scheduled Finish]))),"Past Due/Late",(IIf([Scheduled Finish]>Now(),IIf([Scheduled Start]>Now(),"Future Task","On Track"),"At Risk")))))

2 = Past Due/Late
3 = On Track
4 = At Risk
5 = Complete

The formula works perfectly but for a milestone ([Scheduled Duration]=0) the status shows "at Risk" and should show "Future Task"

Please can you assist me in putting the [Scheduled Duration] = 0 in the correct place so that the formula shows it as a "Future task"

Wednesday, September 10, 2014 7:06 PM

• Albert,

Okay, try the following formula. I didn't thoroughly ring it out but at least it doesn't throw an error and I think it captures the essence of what you are trying to achieve. I did run it against your file and at first glance, everything looks appropriate.

IIf([Scheduled Duration]=0,"",IIf([% Work Complete]=100,"Complete",IIf([Scheduled Start]>now(),"Future Task",IIf([% Work Complete]<100 And [Scheduled Finish]<=now(),"Past due/Late",IIf([% Work Complete]<100*ProjDateDiff(now(),[Scheduled Start])/ProjDateDiff([Scheduled Finish],[Scheduled Start]),"At Risk","On Track")))))

John

• Marked as answer by Thursday, September 11, 2014 5:49 PM
Thursday, September 11, 2014 4:24 PM

### All replies

• Albert,

Well I guess I will be the first to reply.

Indeed if I apply your formula to a simple project with a single task and milestone, it works fine. However, if I apply it to a more complex project, like the one you sent to me, the formula gives a mixture of "Future Task" and "At Risk" for various milestone tasks throughout the file.

The first thing I notice is that the first test (i.e. [Scheduled Duration]=0) does not have the immediate true value but rather it continues on with further tests. I suggest you put "Future Task" immediately after the first test. That will guarantee that all milestones will meet the first test and give a result of "Future Task". If the task is not a milestone, the tests will continue until a true condition is found.

Basically the formula must have the form IIF(test,result if true,result if false)

If I may make a suggestion. A milestone isn't a task at all, so instead of calling it a future task, I suggest you give a result of either blank or "Milestone".

Hope this helps.

John

• Edited by Wednesday, September 10, 2014 7:49 PM test of reasonableness
Wednesday, September 10, 2014 7:35 PM
• Hi John,

You must be sick of me by now.

Is this what you mean:

IIf([Scheduled Duration]=0,"Future Task",IIf([% Complete]=100,"Complete",IIf([Scheduled Finish]<Now()+7 And [% Complete]<100 And [% Complete]>=80,"On Track",IIf([Scheduled Finish]<Now()+7 And [% Complete]<80,"Future Task","At Risk")))),IIf([% Complete]=100,"Complete",IIf([% Complete]>=100*(Abs(ProjDateDiff([Scheduled Start],Now())/ProjDateDiff([Scheduled Start],[Scheduled Finish]))),"Past Due/Late",(IIf([Scheduled Finish]>Now(),IIf([Scheduled Start]>Now(),"Future Task","On Track"),"At Risk")))))

However I get the following error

• Edited by Wednesday, September 10, 2014 8:16 PM
Wednesday, September 10, 2014 8:15 PM
• Albert,

No, you're just a user who need assistance and that's why we are here.

After posting my original reply I continued to look at your formula, since I thought I'd use it in the macro I'm writing for you, and I found a couple other issues. I haven't fully wrung it out, but two areas could be addressed.

First, with a complex formula such as yours it is good practice to look at all the easy conditions first and address those. For example, look for milestones, then look for completed tasks, then look for tasks whose start date is beyond the current date. After those are handled, then the formula can look at other parameters. Remember, a formula is only processed until it hits a "true" condition and then stops.

Second, I think the formula is attempting to look for too many special conditions. For example, it looks at tasks that are coming due and sorts those out by their completion percentage. That's fine, but then the latter part of the formula attempts to look at whether a task is past due/late based on calculated percent complete. I suggest you break the formula into two parts. Delete the the past due/late criteria from the existing formula and put it into a separate custom field. Then get both formulas working and see if there are conflicts.

John

Wednesday, September 10, 2014 10:40 PM
• Hi John,

I have tied myself in a knot.

Please could you correct the formula for me as my coding is letting me down?

It will be appreciated.

Thanks

Albert

Thursday, September 11, 2014 8:46 AM
• Albert,

I hope it's a "slip" knot :-)

I've been thinking more about your formula. Aside from milestones, completed tasks, and future tasks the formula attempts to derive a status based on two potentially opposing ideas. The first looks at the completeness of a task within 7 days of when it is supposed to be finish. It arbitrarily sets the level of completeness at 80%. When you step back and think about it, that type of metric really has no basis, it's subjective. What basis is there for a task to be at least 80%, or any value, within 7 days of its finish? None really. However, the other criteria in the formula attempts to measure status based on how the entered percent complete compares with what the percent complete should be assuming a linear progression. That metric has merit because it is objective rather than subjective.

So, I suggest the formula be simplified to first test for milestones, then for complete tasks, then for future tasks, then compare percent complete against what percent should be complete by the current date. And I would use % Work Complete instead of % Complete because work is what gets things done, the passage of time does not. Leave out the subjective test for 7 days from the current date.

Give that a try. If it continues to elude you, I'll create it, but it won't be until later as I have other priorities.

John

• Edited by Thursday, September 11, 2014 2:59 PM followup
Thursday, September 11, 2014 1:47 PM
• Albert,

Okay, try the following formula. I didn't thoroughly ring it out but at least it doesn't throw an error and I think it captures the essence of what you are trying to achieve. I did run it against your file and at first glance, everything looks appropriate.

IIf([Scheduled Duration]=0,"",IIf([% Work Complete]=100,"Complete",IIf([Scheduled Start]>now(),"Future Task",IIf([% Work Complete]<100 And [Scheduled Finish]<=now(),"Past due/Late",IIf([% Work Complete]<100*ProjDateDiff(now(),[Scheduled Start])/ProjDateDiff([Scheduled Finish],[Scheduled Start]),"At Risk","On Track")))))