Ask a new question

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 September 30, 2021 Views 263 Applies to: