Custom Indicator Using Formula

Hi,

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.

Thanks

David
|
Answer
Answer

Hi David,

using IIF makes things more complicate than using SWITCH:

Switch([% Complete]=100
,1,
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
,2,
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
,3,
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))
,4,
DateAdd('d',14,ProjDateConv(Now(),pjDate_mm_dd_yyyy))<=ProjDateConv([Start],pjDate_mm_dd_yyyy)
,5,
ProjDateConv([Finish],pjDate_mm_dd_yyyy)<ProjDateConv(Now(),pjDate_mm_dd_yyyy) And [% Complete]<100
,6,
True
,7)

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?
Barbara

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 237 Applies to: