# formule error for indicator progress

• ### Question

• Hi Somebody

I have a formule to indicate my progress:

IIf([% Complete]=100;4;(IIf([Current Date]>=[Finish];3;(IIf([Current Date]>[Start] And [% Complete]=0;5;(IIf([Current Date]>[Finish] And [% Complete]=100;2;(IIf([Current Date]<[Start];6;1)))))))))

4=green

3=red

2=amber

5=blue

1=clear

But formule rule is not working on this rule: ;(IIf([Current Date]>[Finish]And [% Complete]=100;2;

Mabey overruling by : IIf([% Complete]=100;4 ?

The problem is, I want the task when ( to late ) still making 100% and not changing to green

Someone a solution?

Rein

Wednesday, March 30, 2016 3:46 PM

• Rein,

Yeah I thought of that change to the formula but unfortunately that version of the formula will flag all tasks that were completed in the past, on time or not, as red. So in effect, everyone who ever completed their task gets "punished". I wouldn't think that is your intent but that is what you will get.

John

• Marked as answer by Friday, April 1, 2016 6:50 AM
Thursday, March 31, 2016 5:27 PM

### All replies

• Rein,

Hello again. Well your right in that if percent complete = 100% then the first criteria is met and the indicator will be green. As soon as a true result is encountered in the formula processing, the processing stops. But I'm real curious, if the task is done (i.e. percent complete = 100%) why you would care if the current date is beyond the task finish date? An incomplete late task would previously have been flagged as red, so the "bad" mark is in the history but now that the task is done, rejoice and move on!

By the way, I see your formula yields a "6" (future task) but there is no color for that.

John

• Edited by Wednesday, March 30, 2016 5:15 PM negotiation
Wednesday, March 30, 2016 5:11 PM
• hoi John

the problem is, all project users have to set his own task 100%, sometimes it wil not make any differents to the successor task,  by example, engineering have to make a drawing for purchase to order something. engineering is 3 day,s late with the drawing, purchase have 2 weeks to order, en if they are finish in 2 weeks, purchase is giving green, and engineering is 3 day,s late ( but 3 days late they are finishing there task, so there is some need for history for kpi and that kind of stuff, so i need the 3 day,s late and 100%

but if you have  a better solution :-), please

Rein

Wednesday, March 30, 2016 6:03 PM
• ps; 6 = yellow :-)
Wednesday, March 30, 2016 6:05 PM
• Rein,

So you want to punish engineering for being late and then rub their face in it for days to come. Yeah, we used to get that all the time.

Be that as it may, I don't see any way to do what you want with a formula. Formulas operate on pure logic but what you want to do is not logical. I still think you best "solution" is to look at the previous report, (i.e. last status report), and see what tasks are late. Since the engineers already took the beating for that, why do you insist on punishing them into the future? Your "history" should be a saved version of the file from the last status.

John

Wednesday, March 30, 2016 7:03 PM
• hi John

it was an example , nothing to do with engineers :-)

but the case is, we have 5 departments, and you can see,in one second, witch department is late, and for the KPI and history, how many days are you late

but if there is no solution, i understand

thanks

Rein

Wednesday, March 30, 2016 7:23 PM
• john, i am still working on the macro you write for me, and this is a guestion from all departments ( this are the milestones i tell you in the last topic rollup )

and also for discussion about the progress every week, thats all

greets

Rein

Wednesday, March 30, 2016 7:29 PM
• Rein,

What is "KPI"?

I guess I'm still lost on your logic. Let's say that your plan is statused weekly, or even daily, it doesn't really matter. If a task was supposed to have started (i.e. Start<Current Date) but has no progress (i.e. % Complete=0), then that task will show as blue, (which in my mind should probably be yellow, using the standard convention of good=green, caution=yellow, problem=red), and the assigned department can be identified and corrective action can be taken.

As far as history is concerned, history is saved versions of the plan after each status period. And if there is some reason you need to compare the current plan with a previous plan, use the Compare Projects utility, although be advised that utility does NOT work with master files, dynamic or static. For reference, I'm working on a macro utility that will allow comparison of master projects. I wrote it years ago but it needs some tweaking. When it's done, I'll publish it as a Wiki article.

John

Thursday, March 31, 2016 2:33 AM
• hi John

KPI = Key Performance Indicator ( indicators for quality and Progress )

i have use this formule by now:

IIf([% Complete]=100 And Not [Current Date]>=[Finish];1;(IIf([Current Date]>=[Finish];2;(IIf([Current Date]>[Start] And [% Complete]=0;3;(IIf([Current Date]<[Start];4)))))))

1=Green

2=Red

3=Blue

4=Clear

so when your make the task 100% after finish date, you get still red, otherwise is Green,

its also a reminder to the users, they have to put there status on 100% at time

thanks for thinking with me

Rein

Thursday, March 31, 2016 3:30 PM
• Rein,

Yeah I thought of that change to the formula but unfortunately that version of the formula will flag all tasks that were completed in the past, on time or not, as red. So in effect, everyone who ever completed their task gets "punished". I wouldn't think that is your intent but that is what you will get.

John

• Marked as answer by Friday, April 1, 2016 6:50 AM
Thursday, March 31, 2016 5:27 PM
• hi John

i know what you mean, but they get what they want:-)

thanks

Rein

Friday, April 1, 2016 6:50 AM