# What is this formula telling me?

• ### Question

• switch((Not (Ucase([Unique ID Predecessors])=Ucase(cstr([Text21])))) And ([Finish]>[Finish7]) And (ProjDateDiff([Start],[Finish])=ProjDateDiff([Start7],[Finish7])) And ([Finish]=0) And ([% Complete]<100),5,(Not (Ucase([Unique ID Predecessors])=Ucase(cstr([Text21])))) And ([Finish]>[Finish7]) And (ProjDateDiff([Start],[Finish])>ProjDateDiff([Start7],[Finish7])) And ([Constraint Type]=0) And ([% Complete]<100),8,(([Start7]=ProjDateValue("NA") Or [Finish]=ProjDateValue("NA")) And [% Complete]<100),3,([Start7]=ProjDateValue("NA") Or [Finish7]=ProjDateValue("NA")) And [% Complete]=100,2,([Finish])<([Finish7]) And ([Constraint Type]>1) And ([Duration]=0) And ([Milestone]=-1) And ([Status Date]<[Finish]),4,(Not (([Start7]=ProjDateValue("NA") Or [Finish7]=ProjDateValue("NA")))) And ((ProjDateDiff([Start],[Finish])>ProjDateDiff([Start7],[Finish7])) And ([% Complete]<100) And ([Status Date]<[Finish])),7,(([Finish7]<[Finish]) And ([Status Date]<[Finish]) And ([Constraint Type]>1) And [% Complete]<100),6,(([Status Date]>[Finish]) And [% Complete]<100),1,True,9)
Wednesday, July 20, 2011 1:48 PM

• Some excellent comments have already been posted. I did some parsing on the inline code as follows:

Some Notes:

Contraint Type and corresponding numerical values

 ASAP 0 ALAP 1 MSO 2 MFO 3 SNET 4 SNLT 5 FNET 6 FNLT 7

[Milestone] returns 0 if No, -1 if Yes

To help readibility, I separated some expressions:

Expression 0: Not (Ucase([Unique ID Predecessors])=Ucase(cstr([Text21])))

Returns False if the content of Unique ID Predecessors field matches the content of Text21

Expression 1: (ProjDateDiff([Start],[Finish])=ProjDateDiff([Start7],[Finish7])

Returns True if the difference between Start & Finish dates (in minutes) is equal to the difference between Start7 & Finish7

Expression 2: (ProjDateDiff([Start],[Finish])>ProjDateDiff([Start7],[Finish7])

Returns True if the difference between Start & Finish dates is GREATER THAN the difference between Start7 & Finish7

----

The complete expression:

switch(

Return 5 if the Expression0 is True, and Finish date is greater than Finish7 date, and Expression1 is True, and Finish date is 0 (???) and the task is not completed (if I am not wrong  –this inline code challenges the eye sight- there is a parenthesis mismatch; count the number of opening ones and closing ones)

(EXPRESSION0) And ([Finish]>[Finish7]) And EXPRESSION1) And ([Finish]=0) And ([% Complete]<100),5,

Return 8 if the Expression0 is True, and Finish date is greater than Finish7 date, and Expression2 is True, and it is an ASAP task and the task is not completed (check for parenthesis mismatching)

(EXPRESSION0) And ([Finish]>[Finish7]) And EXPRESSION2) And ([Constraint Type]=0) And ([% Complete]<100),8,

Return 3 if the task is not completed and either Start7 or Finish7 has no date (no need for extra paranthesis)

(   ([Start7]=ProjDateValue("NA") Or [Finish]=ProjDateValue("NA")) And [%Complete]<100   ),3,

Return 2 if the task is completed and either Start7 or Finish7 has no date

([Start7]=ProjDateValue("NA") Or [Finish7]=ProjDateValue("NA")) And [% Complete]=100,2,

Return 4 if task Finish date is earlier than Finish7 and task has a contraint other than ASAP or ALAP, and task’s duration is zero and it is a milestone and Status Date is earlier than Finish date

([Finish])<([Finish7]) And ([Constraint Type]>1) And ([Duration]=0) And ([Milestone]=-1) And ([Status Date]<[Finish]),4,

Return 7 if either Start7 or Finish7 contains a date value and Expression2 is True and the task is not completed, and Status date is earlier than Finish date

(

Not (([Start7]=ProjDateValue("NA") Or [Finish7]=ProjDateValue("NA"))))

And (EXPRESSION2) And ([% Complete]<100) And ([Status Date]<[Finish])

),7,

Return 6 if Finish7 date is earlier than Finish date and Status date is earlier than Finish date and the task has a contraint other than ASAP or ALAP, and the task is not completed

(

([Finish7]<[Finish]) And ([Status Date]<[Finish]) And ([Constraint Type]>1)

And [% Complete]<100

),6,

Return 1 if Status Date is later than Finish date and the task is not completed

(([Status Date]>[Finish]) And [% Complete]<100),1,

If none of the above expressions is true, then return 9

True,9

)

Just in case note:

And it is always easier to rewrite the code than understanding an existing code. Sorry, I did not make any comments on what the code is for in terms of scheduling. I believe Project has already got a lot of advanced features to analyze the schedule.

Hope this helps.

Regards. --Ismet

• Proposed as answer by Wednesday, November 2, 2011 7:15 AM
• Marked as answer by Saturday, December 24, 2011 10:11 PM
• Edited by Thursday, January 9, 2014 11:58 PM corrected typo...
Thursday, July 21, 2011 8:02 AM

### All replies

• Iron triangle,

That's a very good question. This is a classic demonstration of the reason for not using complex equations as formulae.

I would ask the person from whom you got the file that contains this formula. If that isn't viable then you could try breaking the switch statement down into separate parts and analyze each part.

As a whole however I certainly don't have the time to try and figure this one out. Unless someone else has actually seen this formula before or is game enough to invest time in analyzing it, I'm afraid you are on your own.

By the way, if you do figure it out, I suggest you write an explanation in the Notes field so the next person won't have to go through this again.

Good luck.

John

Wednesday, July 20, 2011 2:54 PM
• Agreed.  Looking at it quickly, there are a lot of variables that need to
be defined.

It seems to be assessing the Start/Finish compared to the Start7/Finish7
fields, then assigning a number 1-9 based on that.  You would have to identify
what per your own business processes has been defined as going into those
fields, as by default they're blank.

Then you would need to translate 1-9 into a label, which presumably is somewhere
in the Project file, or in some documentation.

Without those points of data, it's a meaningless formula.

Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
Wednesday, July 20, 2011 3:45 PM
• There is stuff in there that makes no sense. It checks at some point if [Finish] = projDateValue("NA") which is flat out impossible even in a manually scheduled task in 2010.
Brian Kennemer – DeltaBahn Senior Architect
Wednesday, July 20, 2011 3:50 PM
• If I had to hazard a wild guess, it would be I think it might be searching for incomplete predecessors/successors.  How that compares to Finish7, Text21 and the price of tea in China is unknown.  Seems like the King of Obfuscation has run amok.  Ask the person who wrote the formula what they are trying to accomplish.

If that person is not available, perhaps you can tell us what you do with the formula's result ... I assume it writes to a number or text field.  Then we may be able to assist you.  Perhaps the users of this file perform some magic if the value returned is 5 and other magic if the value is 6.

The shortest answer:  What does this formula do?  It returns values from 0 to 9.

Other interesting questions and observations:

1. UniqueID Predecessors is a string of integers separated by commas, I see no value converting it to UCase

2. I wonder what happens if Text21 contains the same string as "UniqueID Predecessors" but in a different order?

3. Why bother using ProjDateDiff([Start],[Finish] ... that is just [Duration] and is expressed in minutes.  I haven't tested it but it should be in the same units as returned for the same thing using the Start7/Finish7

4. I question the value of [Finish]=0

5. Might this be replaced by using a baseline and something like Finish Variance fields and the like?

If you feel this post answered the question, please vote for it. I am also available here:
msprojectblog.com
Wednesday, July 20, 2011 4:09 PM
• yeah but there is a "True" in there too. Look at the end. LOL

Brian Kennemer – DeltaBahn Senior Architect
Wednesday, July 20, 2011 4:11 PM
• Thanks all for the adive.  Sorry to have wasted everyone's time. The "gentleman" who wrote the formula and used it only on thier project has since moved on.  But, taking Andrew's advice, I started to break it down and I think it was used as some tool to measure something on an schedule that wasn't base lined that should have been.

Taking my own advice and moving on with formulas that the program office actually needs to use.

Wednesday, July 20, 2011 5:28 PM
• Iron Triangle --

Believe me, you DID NOT waste anyone's time in this user forum.  We are all here to help one another.  Your question was a good one, but that formula made my eyes pop out of their sockets!  In spite of the complexity of the formula, got some very good answers from people in this forum who were brave enough to tackle that formula.

Speaking of formulas, if you want a custom field with a formula that determines whether a task has been baselined or not, you could use the following formula in a custom task Flag field:

IIf([Baseline Finish]=ProjDateValue("NA"),False,True)

In this custom Flag field, create the following Graphical Indicator criteria:

Equals        Yes        Green flag
Equals        No          Red flag

Add this custom task Flag field to any task table, such as the Entry table, and you can quickly make sure that you have baselined every new task added to the project through a change control procedure.  Hope this extra helps.

Dale A. Howard [MVP]
VP of Educational Services
msProjectExperts
http://www.msprojectexperts.com
http://www.projectserverexperts.com
"We write the books on Project Server"

Wednesday, July 20, 2011 7:28 PM
• Good point eDale.  Most of the time when I am fishing for "not baselined tasks" it is when I am in "working mode" and not trying to make a report for anyone.

In that case, I just insert the basline start or baselne finish column and then filter to "NA".  Not as pretty, but faster.

If I want to get very fancy, I also insert the ID or UniqueID column next to it.  After running the filter I can compose an email or Word Doc and then paste the ID's into the table (I format the table for 12 columns wide).  Now my report can easily say:  "The following tasks lack a baseline:"  Below that sentence is  table listing task IDs in a oompact format.  It transfers over to PPT, Outlook, Word, Excel quite nicely .... especially in the 2010 versions.

If you feel this post answered the question, please vote for it. I am also available here:
msprojectblog.com
Wednesday, July 20, 2011 10:04 PM
• It also calls into mind an axiom I try to use when coming into an organization that is using project or project server:

If a you cannot tell me which business decisions are informed by a custom field then delete it.

If a custom field is not in a report, delete it.

If a custom field is in a report, find out how its value is used to make decisions. If nobody knows why it is there, delete it.

:-)

Brian Kennemer – DeltaBahn Senior Architect
Wednesday, July 20, 2011 10:11 PM
• Thank you for showing us this classic. I am glad I wasn't paying the guy who wrote this. Unreadable, untestable and unexplainable.
Thursday, July 21, 2011 1:01 AM
• It is an amazingly good thread, I got to learn how an unexplained and complex Formula can lead to trouble. I been a sluggish person never write any notes after a Formula tends to work, LOL. Would improve on.
Sapna S
Thursday, July 21, 2011 7:46 AM
• Some excellent comments have already been posted. I did some parsing on the inline code as follows:

Some Notes:

Contraint Type and corresponding numerical values

 ASAP 0 ALAP 1 MSO 2 MFO 3 SNET 4 SNLT 5 FNET 6 FNLT 7

[Milestone] returns 0 if No, -1 if Yes

To help readibility, I separated some expressions:

Expression 0: Not (Ucase([Unique ID Predecessors])=Ucase(cstr([Text21])))

Returns False if the content of Unique ID Predecessors field matches the content of Text21

Expression 1: (ProjDateDiff([Start],[Finish])=ProjDateDiff([Start7],[Finish7])

Returns True if the difference between Start & Finish dates (in minutes) is equal to the difference between Start7 & Finish7

Expression 2: (ProjDateDiff([Start],[Finish])>ProjDateDiff([Start7],[Finish7])

Returns True if the difference between Start & Finish dates is GREATER THAN the difference between Start7 & Finish7

----

The complete expression:

switch(

Return 5 if the Expression0 is True, and Finish date is greater than Finish7 date, and Expression1 is True, and Finish date is 0 (???) and the task is not completed (if I am not wrong  –this inline code challenges the eye sight- there is a parenthesis mismatch; count the number of opening ones and closing ones)

(EXPRESSION0) And ([Finish]>[Finish7]) And EXPRESSION1) And ([Finish]=0) And ([% Complete]<100),5,

Return 8 if the Expression0 is True, and Finish date is greater than Finish7 date, and Expression2 is True, and it is an ASAP task and the task is not completed (check for parenthesis mismatching)

(EXPRESSION0) And ([Finish]>[Finish7]) And EXPRESSION2) And ([Constraint Type]=0) And ([% Complete]<100),8,

Return 3 if the task is not completed and either Start7 or Finish7 has no date (no need for extra paranthesis)

(   ([Start7]=ProjDateValue("NA") Or [Finish]=ProjDateValue("NA")) And [%Complete]<100   ),3,

Return 2 if the task is completed and either Start7 or Finish7 has no date

([Start7]=ProjDateValue("NA") Or [Finish7]=ProjDateValue("NA")) And [% Complete]=100,2,

Return 4 if task Finish date is earlier than Finish7 and task has a contraint other than ASAP or ALAP, and task’s duration is zero and it is a milestone and Status Date is earlier than Finish date

([Finish])<([Finish7]) And ([Constraint Type]>1) And ([Duration]=0) And ([Milestone]=-1) And ([Status Date]<[Finish]),4,

Return 7 if either Start7 or Finish7 contains a date value and Expression2 is True and the task is not completed, and Status date is earlier than Finish date

(

Not (([Start7]=ProjDateValue("NA") Or [Finish7]=ProjDateValue("NA"))))

And (EXPRESSION2) And ([% Complete]<100) And ([Status Date]<[Finish])

),7,

Return 6 if Finish7 date is earlier than Finish date and Status date is earlier than Finish date and the task has a contraint other than ASAP or ALAP, and the task is not completed

(

([Finish7]<[Finish]) And ([Status Date]<[Finish]) And ([Constraint Type]>1)

And [% Complete]<100

),6,

Return 1 if Status Date is later than Finish date and the task is not completed

(([Status Date]>[Finish]) And [% Complete]<100),1,

If none of the above expressions is true, then return 9

True,9

)

Just in case note:

And it is always easier to rewrite the code than understanding an existing code. Sorry, I did not make any comments on what the code is for in terms of scheduling. I believe Project has already got a lot of advanced features to analyze the schedule.

Hope this helps.

Regards. --Ismet

• Proposed as answer by Wednesday, November 2, 2011 7:15 AM
• Marked as answer by Saturday, December 24, 2011 10:11 PM
• Edited by Thursday, January 9, 2014 11:58 PM corrected typo...
Thursday, July 21, 2011 8:02 AM