Custom Indicator Using Formula


I'm having problems with my formula if you don't mind helping.

I want an IIF to show me values based on different %complete &/or start date.

I'm not in work at the minute so don't have the formula I started with.

I would like 

Value '1' if %complete = 100 (work complete)

Value '2'  if start date < today and finish date > today and % complete > 0 (work in progress)

Value '3' if start date < today and finish date > today and % complete = 0 (work not started, should be in progress)

Value '4' if start date  > today and start date < today +14 (work begins within two weeks)

Value '5' if start date > today + 14 (work begins over a fortnight)

Value '6' if finish date < today and % complete <100 (incomplete passed deadline)

Each of these would then result in a different graphical indicator. 

I got a deadline in work and in my haste nominated myself to build this into plan.  Now realised a little more complex than I envisaged.  I'm not used to the formula used in MS project.



Hi David,

using IIF makes things more complicate than using SWITCH:

Switch([% Complete]=100
ProjDateConv([Start],pjDate_mm_dd_yyyy)<=ProjDateConv(Now(),pjDate_mm_dd_yyyy) And ProjDateConv([Finish],pjDate_mm_dd_yyyy)>=ProjDateConv(Now(),pjDate_mm_dd_yyyy) And [% Complete]>0
ProjDateConv([Start],pjDate_mm_dd_yyyy)<=ProjDateConv(Now(),pjDate_mm_dd_yyyy) And ProjDateConv([Finish],pjDate_mm_dd_yyyy)>=ProjDateConv(Now(),pjDate_mm_dd_yyyy) And [% Complete]=0
ProjDateConv([Start],pjDate_mm_dd_yyyy)>=ProjDateConv(Now(),pjDate_mm_dd_yyyy) And ProjDateConv([Start],pjDate_mm_dd_yyyy)<=DateAdd('d',14,ProjDateConv(Now(),pjDate_mm_dd_yyyy))
ProjDateConv([Finish],pjDate_mm_dd_yyyy)<ProjDateConv(Now(),pjDate_mm_dd_yyyy) And [% Complete]<100

I assume you are not interested in time of today, I added ProjDateConv to all Date fields to ignore time. Moreover, I changed some > to >=, ..., you may need to rework this.

The last part "TRUE,7" avoids errors. If none of the previous comparisons is valid, TRUE will be true and give you a 7 - indicationg, that a codition is missing.

Does that help?

Was this reply helpful?


Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.


Question Info

Last updated January 30, 2018 Views 247 Applies to: